SQL for a Join that contains a condition within one of the join conditions

Question

I have a requirement to do a left join between two tables. TableA is a transactional table while TableB contains reference data. The logical rule for my join is as follow:

SELECT *
FROM
    TableA a
    LEFT JOIN TableB b
        ON a.ItemCode = b.ItemCode
        AND a.ItemType = b.ItemType
        AND b.FundID = 1 (but if no match found use b.FundID = 99)

The last join condition, the part in brackets, is what I'm having trouble with.

EDIT: Some clarification - If no match is found on ItemCode & ItemType & FundID = 1 then I want to join on ItemCode & ItemType & FundID = 99. Also TableB might have two records that match on both ItemCode and ItemType with one record having a FundID = 1 and the second record having FundID = 2. I that case I only want the record with FundID = 1.

What would be the most efficient way to write this query?

The only thing I can come up with is to execute the query twice, once with FundID = 1 and then with FundID = 99. Then use a set operator to return all the records form the first query and only records from the second query that does not exist in the first one. The code will not be pretty and it does not seem efficient either.

Thanks for your ideas in advance.

Marius

Add a column to create a `Row_Number()` partitioned on `ItemType` and `ItemCode` and ordered by `FundId`, then use only the results with row number `1`? — HABO, Apr 06 '17 at 03:36
when using `b.FundID = 99`, do you still required the condition on `ItemCode` & `Itemtype` ? — Squirrel, Apr 06 '17 at 03:37
@Squirrel Yes, when using `b.FundID = 99`, I still require the condition on `ItemCode` and `ItemType` — Marius, Apr 06 '17 at 03:57
@Marius Then the answer given by TriV should work, and it also means the language in your question is not clear. — Tim Biegeleisen, Apr 06 '17 at 03:57
use `b.fundID IN (1, 99)` will return rows with fundID =1 and also fundID = 99. Is that what you want ? — Squirrel, Apr 06 '17 at 04:01

score 1 · Answer 1 · answered Apr 06 '17 at 04:15

If i do understand your requirement correctly, this should gives you what you want

SELECT *
FROM
    TableA a
    OUTER APPLY
    (
        SELECT TOP 1 *
        FROM   TableB b
        WHERE a.ItemCode = b.ItemCode
        AND  a.ItemType = b.ItemType
        AND   b.FundID IN (1, 99)
        ORDER BY b.FundID
    ) b

score 0 · Answer 2 · answered Apr 06 '17 at 03:26

0

You can change the query to

AND b.FundID IN (1,99)

or

AND (b.FundID = 1 or b.FundID = 99)

answered Apr 06 '17 at 03:26

TriV

5,118
2
10
18

That will not work. The rule is to join on FundID = 1 and only join on FundID = 99 if that does not work. There might be matching records for both FundID 1 and 99 - in that case I only need to bring back the matching record where FundID = 1 and not 99. – Marius Apr 06 '17 at 03:29
If FundID = 1 then it can not be 99. Could you add some input and output data, it's more clear. – TriV Apr 06 '17 at 03:36
The OP is saying that if the first three conditions fail, then _only_ `FundID = 99` is required to make a match. Your answer does not agree with this. – Tim Biegeleisen Apr 06 '17 at 03:38
OP needs confirm this require. I don't think it doesn't need two other conditions – TriV Apr 06 '17 at 03:40

score 0 · Answer 3 · answered Apr 06 '17 at 05:14

0

This is the best solution I have received so far. Thanks to @HABO (see the comments section of my question).

Add a column to create a Row_Number() partitioned on ItemType and ItemCode and ordered by FundId, then use only the results with row number 1

answered Apr 06 '17 at 05:14

Marius

208
5
14

Happy to help, sorry I didn't have time to write a complete answer. You ought to edit your answer to include the query you created for the benefit of others who stumble across your question in the future. And collect some upvotes. – HABO Apr 06 '17 at 13:40

score 0 · Answer 4 · answered Apr 08 '17 at 17:27

For posterity:

-- Sample data.
declare @TableA as Table ( AId Int Identity, ItemCode VarChar(20), ItemType VarChar(20) );
declare @TableB as Table ( BId Int Identity, ItemCode VarChar(20), ItemType VarChar(20), FundId Int );

insert into @TableA ( ItemCode, ItemType ) values
  ( 'Nemo', 'Fish' ), ( 'Blinky', 'Fish' ), ( 'Muddy Mudskipper', 'Fish' ),
  ( 'Hammer', 'Tool' ), ( 'Screwdriver', 'Tool' ), ( 'Politician', 'Tool' ),
  ( 'Grape Nehi', 'Beverage' ), ( 'Screwdriver', 'Beverage' );
insert into @TableB ( ItemCode, ItemType, FundId ) values
  ( 'Blinky', 'Fish', 1 ), ( 'Muddy Mudskipper', 'Fish', 2 ),
  ( 'Hammer', 'Tool', 1 ), ( 'Screwdriver', 'Tool', 99 ),
  ( 'Politician', 'Tool', 1 ), ( 'Politician', 'Tool', 99 ),
  ( 'Grape Nehi', 'Beverage', 42 ), ( 'Screwdriver', 'Beverage', 1 );
select * from @TableA;
select * from @TableB;

-- Do the deed.
with JoinWithRanking as (
  select A.AId, A.ItemCode, A.ItemType, B.BId, B.FundId,
    Row_Number() over ( partition by A.ItemCode, A.ItemType order by B.FundId ) as RN
    from @TableA as A left outer join
      @TableB as B on B.ItemCode = A.ItemCode and B.ItemType = A.ItemType and
        B.FundId in ( 1, 99 )
  )
  select AId, ItemCode, ItemType, BId, FundId
    from JoinWithRanking
    where RN = 1;

SQL for a Join that contains a condition within one of the join conditions

4 Answers4