sqljoinssms-16

Trying to look up records based on a join

I'm trying to work on a stored procedure that is somewhat tricky, let's say I have Table_1 with this data:

Num1         Name1         Code1      Desc
-------------------------------------------
123B         Apple         10         Text1
123B         Apple         11         Text1
123C         Google        20         Text2

I also have a lookup table that looks like this:

Tbl_LookUp

Num1        Code1
-------------------
123B        10
123C        25

So what I am trying to do in this scenario is:

Select data from Table_1 WHERE:

  1. There is a match between Table_1 and Tbl_Lookup on Num1

and

  1. If there is a more than 1 record for a particular Num1 in Table_1, then only return the row where Table_1.Code1=Tbl_Lookup.Code1

  2. Otherwise, if there is only 1 record for a particular Num1 in Table_1, then even if the Table_1.Code1 = Tbl_Lookup.Code1 does not work, still return the record.

Desired end result:

Num1         Name1         Code1      Desc
--------------------------------------------
123B         Apple         10         Text1
123C         Google        20         Text2

123B is returned because there are multiple records for this Num1. One of them has the Code1 that corresponds to Tbl_Lookup.Code1

123C is returned, because although the Code1 does not match Tbl_Lookup, there is only one record, so in that case join doesn't matter, and we still want to return it.

Any help is greatly appreciated.

Solution

  • Not sure if there is a better way to do this. But this should give you want you are looking for

    select t.*
from table1 t
join Tbl_LookUp l on l.Num1 = t.Num1
where t.code1 = l.code1 
or exists ( select count(1) from table1 i
           where i.Num1= t.Num1 
           group by  Num1  
           having count(Num1) = 1   )