Trying to look up records based on a join

Question

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

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

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

Answer 1

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   )
      

Answer 2

Yet another way to obtain the desired results - identify exact lookup matches with exists and count occurences of num1, then allow any with a count of 1 or only matches on both columns where more than 1:

select num1, name1, code1, [desc]
from (
    select * , case when exists (select * from [lookup] l where l.num1 = t.num1 and l.code1 = t.code1) then 1 end lmatch, Count(*) over (partition by num1) cnt
    from t1 t 
    where exists (select * from [lookup] l where l.num1 = t.num1)
)x
where lmatch = 1 and cnt > 1 or cnt = 1;

Answer 3

This is a great place to use apply:

select t1.*
from tbl_lookup l cross apply
     (select top (1) t1.*
      from table1 t1
      where t1.num1 = l.num1
      order by (case when t.code = l.code1 then 1 else 2 end)
     );

Answer 4

One way is

select t.Num1, t.Name1, t.Code1, t.Desc
from (
    select Num1, Name1, Code1, Desc, 
       count(code1) over(partition by Num1) cnt
    from Table_1 ) t
join Tbl_Lookup tl on t.Num1 = tl.Num1
    and (t.cnt = 1 or t.Code1 = tl.Code1)