October 22, 2019 at 6:25 pm
Hi,
I am not an SQL expert and hence looking for a solution to one of my problem.
I have 3 tables. On joining I am getting cartesian product which I do not need.
Instead I want non-repeated values.
I have attached the sample tables. Can someone help me for it?
Thanks.
October 22, 2019 at 7:03 pm
Instead of
SELECT cols
...
Try
SELECT DISTINCT cols
...
Does that help?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 22, 2019 at 7:17 pm
You haven't shown the query you have written or the results you are getting.
SELECT t1.ID, t1.Name, t2.ValueA, t3.ValueB
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ID = t1.ID
INNER JOIN Table3 t3
ON t3.ID = t1.ID
AND t3.ID = t2.ID
ORDER BY 1, 2, 3, 4
I can't imagine what query you need to get 'N/A' in ValueB, for example, with Mike there are joins on all the tables which would give you 'CA1' where you have 'N/A'.
October 22, 2019 at 8:27 pm
I think this is what he is looking for.
WITH t2 AS
(
SELECT t2.ID, t2.ValueA, ROW_NUMBER() OVER(PARTITION BY t2.ID ORDER BY t2.ValueA) AS rn
FROM Table2 AS t2
)
, t3 AS
(
SELECT t3.ID, t3.ValueB, ROW_NUMBER() OVER(PARTITION BY t3.ID ORDER BY t3.ValueB) AS rn
FROM Table3 AS t3
)
SELECT t1.ID, t1.Name, t2.ValueA, t3.ValueB
FROM Table1 t1
INNER JOIN t2
ON t2.ID = t1.ID
LEFT OUTER JOIN t3
ON t3.ID = t2.ID
AND t3.rn = t2.rn
ORDER BY t1.ID, t1.Name, t2.rn
Note that equality is transitive, so that if t2.ID = t1.ID and t3.ID = t2.ID, then t3.ID = t1.ID MUST be true and you don't need to specify it in your query.
Also, you should NEVER use ordinal column positions in published code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply