Tricky SQL query

Archived Topics
1

I would appreciate help with the following SQL problem. I have 2 tables as follows:[code]Test_1:

id

a
b
c[/code][code]Test_2:

id Test_1_id Comment Finished

1 a Test a
2 b Test b1 Y
3 b Test b2
[/code]I want to join the tables together to get the following result:[code]id Finished

a
b Y
c[/code]What I’m trying to do is return all the values from table Test_1, along with the value of Finished (if any) from table Test_2. The problem I have is picking out the entry with a value for Finished for ‘b’ in preference to the entry with no value for Finished.

If I try a standard join like SELECT test_1.id FROM test_1 LEFT JOIN test_2 ON Test_1.id=test_2.test_1_idI get the following resultset:[code]id Finished

a NULL
b Y
b NULL
c NULL[/code]Any ideas?

2

You can use the GROUP BY clause to only get one result per id. Using this:

SELECT test_1.id, MAX(CASE WHEN test_2.Finished is NULL THEN '' ELSE test_2.Finished END) as 'Finished' FROM test.test_1 LEFT JOIN test.test_2 ON Test_1.id=test_2.test_1_id GROUP BY test_1.id on this table:

[code]id test_1_id comment Finished

1 a Test a
2 b test b1 Y
3 b test b2
4 c test c1
5 c test c2 Y[/code]
Will return this:

[code]id Finished

a
b Y
c Y[/code]

3

my totally untested try:

SELECT test_1.id, test.2.Finished
FROM test_1 LEFT JOIN test_2
ON Test_1.id=test_2.test_1_id and test2.Finished=‘Y’

That should return all rows from test_1 and only those rows from test_2 that are Finished.

The difference between mine and Bobbys’ is given this:

[code]id test_1_id comment Finished

1 a Test a
2 b test b1 Y
3 b test b2 Y
4 c test c1
5 c test c2 Y[/code]

my code will give:

[code]id Finished

a
b Y
b Y
c Y[/code]

and Bobbys’ will give:

[code]id Finished

a
b Y
c Y[/code]

4

Thanks for the replies :thumb_left:

Robert , I think you are correct in pointing out that your method (which initially looked perfect!) runs the risk of still returning multiple values. Bobby’s method makes sure that only one value is returned.

5

I guess it depends on what your after.
If you only care that a test passed of if you want to know which run passed (in which case you probaly also want the comment column in the result set).