CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Marius
Marius

Reputation: 208

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

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

Upvotes: 0

Views: 91

Answers (4)

HABO
HABO

Reputation: 15816

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;

Upvotes: 0

Marius
Marius

Reputation: 208

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

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 1

TriV
TriV

Reputation: 5148

You can change the query to

AND b.FundID IN (1,99)

or 

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

Upvotes: 0

Related Questions