Left Joins that link to multiple rows only returning one

Question

I'm trying to join two table (call them table1 and table2) but only return 1 entry for each match. In table2, there is a column called 'current' that is either 'y', 'n', or 'null'. I have left joined the two tables and put a where clause to get me the 'y' and 'null' instances, those are easy. I need help to get the rows that join to rows that only have a 'n' to return one instance of a 'none' or 'null'. Here is an example

table1 ID
1
2
3

table2
ID | table1ID | current
1 | 1 | y
2 | 2 | null
3 | 3 | n
4 | 3 | n
5 | 3 | n

My current query joins on table1.ID=table2.table1ID and then has a where clause (where table2.current = 'y' or table2.current = 'null') but that doesn't work when there is no 'y' and the value isn't 'null'.

Can someone come up with a query that would join the table like I have but get me all 3 records from table1 like this?

Query Return

ID | table2ID | current
1 | 1 | y
2 | null | null
3 | 3 | null or none

Answer 1

you need to decide which of the three rows from table2 with table1id = 3 you want:

3 | 3 | n
4 | 3 | n
5 | 3 | n

what's the criterion?

Answer 2

First off, I'm assuming the "null" values are actually strings and not the DB value NULL. If so, this query below should work (notice the inclusing of the where criteria INSIDE the ON sub-clause)

select 
table1.ID as ID
,table2.ID as table2ID
,table2.current 
from table1 left outer join table2 
on (table2.table1ID = table1.ID and 
(table2.current in ('y','null'))

If this does work, I would STRONGLY recommend changing the "null" string value to something else as it is entirely misleading... you or some other developer will lose time debugging this in the future.

If "null" acutally refers to the null value, then change the above query to:

select 
table1.ID as ID
,table2.ID as table2ID
,table2.current 
from table1 left outer join table2 
on (table2.table1ID = table1.ID and 
(table2.current = 'y' or table2.current is null))

Answer 3

select t1.id
     , t2.id
     , case when t2.count_current > 0 then
           t2.count_current 
       else
           null
       end as current
from table1 t1
left outer join
(
  select id
  , max(table1id)
  , sum(case when current = 'y' then 1 else 0 end) as count_current
  from table2
  group by id
) t2
on t1.id = t2.table1id

although, as justsomebody has pointed out, this may not work as you expect once you have multiple rows with 'y' in your table 2.