November 18, 2015 at 10:26 am
I am joining 3 tables in SQL. There should always be a AccountID for ProductID1, but not necessarily AccountIDs for ProductID2 or ProductID3.
SELECT
i.AccountID AS [ProductID1],
o.AccountID AS [ProductID2],
a.AccountID AS [ProductID3]
FROM [dbo].[Product1] i
LEFT OUTER JOIN [dbo].[Product2] o
ON i.ProductAccountID = o.AccountID
LEFT OUTER JOIN [dbo].[Product3] a
ON i.ProductAccountID = a.AccountID
My results return like this.
Product1Product2Product3
35 NULL NULL
35 NULL 290
35 659 NULL
14 NULL NULL
14 NULL 296
40 NULL NULL
I want it to return like this.
Product1Product2Product3
35 659 290
14 NULL 296
40 NULL NULL
How can I do this?
November 18, 2015 at 10:40 am
Your data doesn't cover all possible cases, so it's not clear which approach to use. What do you want to happen with the following data:
Product1Product2Product3
35NULLNULL
35NULL290
35NULL300
35450NULL
35500350
35659NULL
14NULLNULL
14NULL296
40 NULL NULL
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 18, 2015 at 10:41 am
maybe...
SELECT P1, MAX(P2) AS MP2, MAX(P3) AS MP3
FROM
(SELECT 35 AS P1,NULL AS P2,NULL AS P3
UNION ALL SELECT 35,NULL,290
UNION ALL SELECT 35, 659,NULL
UNION ALL SELECT 14,NULL,NULL
UNION ALL SELECT 14,NULL,296
UNION ALL SELECT 40, NULL, NULL) x
GROUP BY P1
ORDER BY P1;
November 18, 2015 at 11:04 am
There will never be more than 1 AccountID for any of the products. For example you have with Product1 35 it could only have Product 2 of 450 or NULL and Product 3 290 or NULL.
Product1Product2Product3
35NULLNULL
35NULL290
35NULL300
35450NULL
35500350
35659NULL
14NULLNULL
14NULL296
40 NULL NULL
November 18, 2015 at 11:23 am
Where's the ProductID in your dataset? Either my psychic powers or my eyesight must be failing.
How did my solution not work?
November 18, 2015 at 12:00 pm
Piet's on the right track. Try the following:
SELECT
i.AccountID AS [ProductID1],
MAX(o.AccountID) AS [ProductID2],
MAX(a.AccountID) AS [ProductID3]
FROM [Product1] i
LEFT OUTER JOIN [Product2] o
ON i.ProductAccountID = o.AccountID
LEFT OUTER JOIN [Product3] a
ON i.ProductAccountID = a.AccountID
GROUP BY i.AccountID
It does seem strange that you have 3 separate product tables.
Drew
PS: You're more likely to get better, quicker responses if you follow the Forum Etiquette: How to post data/code on a forum to get the best help[/url]
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 18, 2015 at 12:26 pm
It did work. Thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply