SQL Union Help

I have this UNION statement that shows contract price and normal price.  Some items don't have contract prices.  I'm needing a report that when there is a contract price to show it and only that price.  If there is no contract price then show Price1.  The below script will show dups one with the contract price and one with the  Price1.  

If there is a better way to do this other then a Union I'm open to it.

--to get contract price
SELECT DISTINCT inv_loc.location_id, inv_mast.item_id, inv_mast.item_desc, job_price_line.price, job_price_hdr.contract_no
FROM     job_price_hdr INNER JOIN
                  job_price_line ON job_price_hdr.job_price_hdr_uid = job_price_line.job_price_hdr_uid RIGHT OUTER JOIN
                  inv_loc INNER JOIN
                  inv_mast ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid ON job_price_line.inv_mast_uid = inv_mast.inv_mast_uid
WHERE  (inv_loc.location_id = '9') AND (job_price_hdr.approved = 'y') AND (job_price_hdr.cancelled = 'n') AND (job_price_hdr.corp_address_id = '101') AND 
                  (job_price_hdr.end_date > GETDATE())

UNION

--All price
SELECT DISTINCT inv_loc.location_id, inv_mast.item_id, inv_mast.item_desc, inv_loc.price1 as price, ''
FROM     inv_loc INNER JOIN
                  inv_mast ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
WHERE  (inv_loc.location_id = '9') AND (inv_mast.delete_flag = 'n')

Open in new window



This is the result

location_id                             item_id                                  item_desc                                price                                   contract_no
--------------------------------------- ---------------------------------------- ---------------------------------------- --------------------------------------- ---------------------------
9                                        Apples                                 Apples - Large                                           .350000000                            
9                                        Apples                                 Apples - Large                                           .290000000                            5
9                                        Oranges                              Orange - Small                                          .300000000                            
9                                        Oranges                              Orange - Small                                          .250000000                            5
9                                        Bananas                              Bananas  - normal                                   .180000000                               

Open in new window



Example of what I'm looking for.

location_id                             item_id                                  item_desc                                price                                   contract_no
--------------------------------------- ---------------------------------------- ---------------------------------------- --------------------------------------- ---------------------------
9                                        Apples                                 Apples - Large                                           .290000000                            5
9                                        Oranges                              Orange - Small                                          .250000000                            5
9                                        Bananas                              Bananas  - normal                                   .180000000                               

Open in new window

LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
assuming you're using MS SQL, you can try this like this:

;with cte as (
--to get contract price
 SELECT DISTINCT inv_loc.location_id, inv_mast.item_id, inv_mast.item_desc, job_price_line.price, job_price_hdr.contract_no, 0 idx FROM job_price_hdr INNER JOIN job_price_line ON job_price_hdr.job_price_hdr_uid = job_price_line.job_price_hdr_uid RIGHT OUTER JOIN inv_loc INNER JOIN inv_mast ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid ON job_price_line.inv_mast_uid = inv_mast.inv_mast_uid WHERE (inv_loc.location_id = '9') AND (job_price_hdr.approved = 'y') AND (job_price_hdr.cancelled = 'n') AND (job_price_hdr.corp_address_id = '101') AND (job_price_hdr.end_date > GETDATE())
UNION
--All price
SELECT DISTINCT inv_loc.location_id, inv_mast.item_id, inv_mast.item_desc, inv_loc.price1 as price, '', 1 idx FROM inv_loc INNER JOIN inv_mast ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid WHERE (inv_loc.location_id = '9') AND (inv_mast.delete_flag = 'n')
), cte2 as (
 select a.*, row_number() over (partition by location_id, item_id order by idx ) rk  from cte a
)
select * from cte2 where rk = 1
0
Pawan KumarDatabase ExpertCommented:
Do not use SO MANY DISTINCT'S and UNION, THEN RANKING FUNCTION, that will kill the performance of the system. That's not a good idea.

Please use this SIMPLE APPROACH of UNION ALL and GROUP BY.

SELECT location_id,item_id,MAX(item_desc)item_desc,MIN(price) price, MAX(contract_no) contract_no
FROM 
(
	SELECT inv_loc.location_id, inv_mast.item_id, inv_mast.item_desc, job_price_line.price, job_price_hdr.contract_no
	FROM     job_price_hdr INNER JOIN
					  job_price_line ON job_price_hdr.job_price_hdr_uid = job_price_line.job_price_hdr_uid RIGHT OUTER JOIN
					  inv_loc INNER JOIN
					  inv_mast ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid ON job_price_line.inv_mast_uid = inv_mast.inv_mast_uid
	WHERE  (inv_loc.location_id = '9') AND (job_price_hdr.approved = 'y') AND (job_price_hdr.cancelled = 'n') AND (job_price_hdr.corp_address_id = '101') AND 
					  (job_price_hdr.end_date > GETDATE())

	UNION ALL

	SELECT inv_loc.location_id, inv_mast.item_id, inv_mast.item_desc, inv_loc.price1 as price, ''
	FROM     inv_loc INNER JOIN
					  inv_mast ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
	WHERE  (inv_loc.location_id = '9') AND (inv_mast.delete_flag = 'n')
)m GROUP BY location_id,item_id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abarefootAuthor Commented:
Pawan,


I think that's what I'm looking for.  I'll review the data tomorrow.

Thanks again!
0
awking00Commented:
>> I'm needing a report that when there is a contract price to show it and only that price.  If there is no contract price then show Price1<<
Assuming job_price_line.price represents the contract price and inv_loc.price1 represents the price1, you can use coalesce(job_price_line.price, inv_loc.price1) as price.
0
abarefootAuthor Commented:
Thanks this was perfect.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.