471,083 Members | 1,130 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,083 software developers and data experts.

Need Query without Union and minus.

Hi,

We need a query which retrieves option_code, for a plan from OPTION
table which are unique in (market, "ALL").
I have an example below
PLAN MARKET OPTION_CODE RATE
PLAN1 TEXAS 1000 1
PLAN1 TEXAS 2000 2
PLAN1 ALL 1000 3
PLAN1 ALL 3000 4

When we ran the SQL, for PLAN1, we need to get
OPTION_CODE RATE
1000 1
2000 2
3000 4

Currently we are using union and minus to retrieve unique options in
both markets.
select option_code, rate from OPTION
where plan = 'PLAN1'
and market= 'TEXAS'
UNION
(
select option_code, rate from OPTION
where plan = 'PLAN1'
and market= 'ALL'
MINUS
select option_code, rate from OPTION
where plan = 'PLAN1'
and market= 'TEXAS'
)

Is there any way to do without using union and minus. We tried to use
NOT EXISTS, but it is slowing the query...

Thanks in advance for the help......
Jul 19 '05 #1
1 5736
union and minus are the best (most efficient) way to do it. You shouldn't be
trying to avoid them.

Tim
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by krystoffff | last post: by
2 posts views Thread by sumit.sharma | last post: by
2 posts views Thread by Peter Dunker | last post: by
1 post views Thread by Anna | last post: by
1 post views Thread by Rama S | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.