Select values from subquery without duplicates sql

Question

I have table, where type A has two values and type B has two values

A    |   B
-----|--------
val1 | val1
val2 | val2

I do subquery but I get dubolicate values,it looks like this

A    |   B
-----|--------
val1 | val1
val1 | val2
val2 | val1
val2 | val2

My query:

SELECT A, B
FROM 
(SELECT DISTINCT title as A
FROM TABLE1 
INNER JOIN TABLE2 ON TABLE1_TABLE2_ID = TABLE2_ID
INNER JOIN TABLE3 ON TABLE1_TABLE3_ID = TABLE3_ID
WHERE TABLE_TABLE3_ID = 2008 AND TABLE_TYP LIKE 'A' AND TABLE1_AKTIV = 'Y')
LEFT JOIN
(SELECT DISTINCT title as B
FROM TABLE1 
INNER JOIN TABLE2 ON TABLE1_TABLE2_ID = TABLE2_ID
INNER JOIN TABLE3 ON TABLE1_TABLE3_ID = TABLE3_ID
WHERE TABLE_TABLE3_ID = 2008 AND TABLE_TYP LIKE 'B' AND TABLE1_AKTIV = 'Y')
ON 1 = 1

Have you any Ideas how I can do it without duplicates?

You might get a more efficient solution if you provided a minimal reproducible example including your table structures and some sample data so that we can replicate your output. Doing the same queries with one parameter change and joining is very inefficient and there is almost certainly a way to speed it up. — MT0
– MT0, Commented Nov 14, 2017 at 10:29

Bhanuchander Udhayakumar · Accepted Answer · 2017-11-14 07:33:35Z

1

The problem is ON 1=1. This will get you all combinations. you need to change query with proper reference column some thing like ON A.id=B.id.

answered Nov 14, 2017 at 7:33

Bhanuchander Udhayakumar

1,6761 gold badge17 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bhanuchander Udhayakumar Over a year ago

@EmmaW. It is just the join condition where all combinations are true. See this example : bhanuchander210.github.io/SQL-Left-Join

Collectives™ on Stack Overflow

Select values from subquery without duplicates sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related