# Tricky SQL query

**URL:** <https://forum.inductiveautomation.com/t/tricky-sql-query/1984>\
**Category:** Archived Topics\
**Created:** [June 28, 2010, 7:13pm UTC](https://forum.inductiveautomation.com/t/tricky-sql-query/1984 "2010-06-28T19:13:41Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [June 28, 2010, 7:13pm UTC](https://forum.inductiveautomation.com/t/tricky-sql-query/1984/1 "2010-06-28T19:13:41Z")

</div>

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_id`I get the following resultset:[code]id Finished

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

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [June 28, 2010, 8:05pm UTC](https://forum.inductiveautomation.com/t/tricky-sql-query/1984/2 "2010-06-28T20:05:24Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![Robert](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@Robert](https://forum.inductiveautomation.com/u/Robert)\
**Post date:** [June 28, 2010, 8:11pm UTC](https://forum.inductiveautomation.com/t/tricky-sql-query/1984/3 "2010-06-28T20:11:01Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [June 28, 2010, 8:57pm UTC](https://forum.inductiveautomation.com/t/tricky-sql-query/1984/4 "2010-06-28T20:57:02Z")

</div>

Thanks for the replies ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":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.

---

<div class="post-metadata">

**Author:** ![Robert](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@Robert](https://forum.inductiveautomation.com/u/Robert)\
**Post date:** [June 29, 2010, 3:43am UTC](https://forum.inductiveautomation.com/t/tricky-sql-query/1984/5 "2010-06-29T03:43:40Z")

</div>

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).
