January 17, 2020 at 1:16 pm
Hi,
I am not actually a programming but my job did need to write a bit of sql queries. Hope someone can help me
here is my code
select i.item_code,i.cost_standard,(select item_price.item_price where item_price.price_group = 'wholesale') as A,(select item_price.item_price where item_price.price_group = 'vnd')as B
from item i left join item_price on i.item_code = item_price.item_code
where i.item_code in ('SOWL-SGL17173-C1')
result as follow
item_code cost_standard A B
SOWL-SGL17173-C1 63.300000 NULL NULL
SOWL-SGL17173-C1 63.300000 NULL NULL
SOWL-SGL17173-C1 63.300000 NULL NULL
SOWL-SGL17173-C1 63.300000 NULL NULL
SOWL-SGL17173-C1 63.300000 NULL 71.280000
SOWL-SGL17173-C1 63.300000 110.000000 NULL
What i want is all in single line as below and null is not showing
item_code cost_standard A B
SOWL-SGL17173-C1 63.300000 110.000000 71.280000
Please advise.
January 17, 2020 at 2:01 pm
Without a full sample mock up of the tables in question, is it simply just a case of adding MAX and GROUP BY
select
i.item_code,
i.cost_standard,
max((select item_price.item_price where item_price.price_group = 'wholesale')) as A,
max((select item_price.item_price where item_price.price_group = 'vnd'))as B
from item i left join item_price on i.item_code = item_price.item_code
where i.item_code in ('SOWL-SGL17173-C1')
group by i.item_code, i.cost_standard
http://www.sqlservercentral.com/articles/Best+Practices/61537/ If you can follow this link on how to post code for the best results, it helps us to get a feel for what your actually after.
January 17, 2020 at 2:21 pm
Without the complete data set, I'm guessing a little, but try something like this:
SELECT i.item_code,
i.cost_standard,
ipw.item_price,
ipv.item_price
FROM item AS i
LEFT JOIN item_price AS ipw
ON i.item_code = ipw.item_code
AND ipw.price_group = 'wholesale'
LEFT JOIN item_price AS ipv
ON i.item_code = ipv.item_code
AND ipv.price_group = 'vnd'
WHERE i.item_code IN ( 'SOWL-SGL17173-C1' );
You may still see NULL values depending on the data. Possibly adding a GROUP BY can eliminate those if needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy