SQL Server Conditional JOIN two tables

Question

I have 3 Tables:

TableA(IDRem, IDPed, IDOP)
TableB(IDOP, IDPed)
TableC(IDPed, InvoiceDate)

I need a select to JOIN the records of TableA and TableC, but there are two possible conditions:

IF IDPed on TableA IS NOT NULL, then join directly to TableC by IDPed

ID IDPed on TableA IS NULL, then join to TableB by IDOp, and then join TableB to TableC by IDPed

So far i try this:

SELECT
    TableA.*
    ,(CASE WHEN TableC.InvoiceDate IS NULL
        THEN TableC2.InvoiceDate
        ELSE TableC.InvoiceDate
    END) AS InvoiceDate
FROM
    TableA
    LEFT JOIN TableC on TableA.IDPed = TableC.IDPed
    LEFT JOIN TableB on TableB.IDOp = TableA.IDOp
    INNER JOIN TableC as TableC2 on TableC2.IDPed = TableB.IDPed

The problem with this is that every field o tableA I want to include in the select I need to do a case...when to determine if the origin is tableA or TableA2.

Is there a better way of doing this? Thanks!

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

For complex joins you are better served in TSQL using cross apply:

When should I use Cross Apply over Inner Join?

select IDRem, IDPed, IDOP from TableA a
cross apply(
    select IDOP, IDPed from TableB binner
    where a.IDop = binner.IDop
) b
cross apply(
    select IDPed, InvoiceDate cinner
    where b.IDPed = cinner.IDPed
) c
where ...

Strictly psuedo-code but should give you a start.

edited May 23 '17 at 12:14

Community

1
1

answered Apr 29 '15 at 23:10

garryp

5,508
1
29
41

mmmm i never use Cross Apply before, can you give a basic sample on how to use it in this case? thanks! – ericpap Apr 29 '15 at 23:13
I've added a quick example (disclaimer I haven't checked it so may not be perfect). – garryp Apr 29 '15 at 23:25
Have a look at this article: http://sqlserverplanet.com/sql-2005/cross-apply-explained – garryp Apr 29 '15 at 23:26
first part worked: select * from TableA a cross apply( select IDOp from Tableb b where a.IDOp = b.IDOp ) b but if try to do the second cross i get no results – ericpap Apr 29 '15 at 23:42
I also try the second table with outer cross with no luck – ericpap Apr 29 '15 at 23:43

Felix Pamittan · Answer 2 · 2015-04-30T00:44:33.560

1

You could try doing both JOINs and use UNION ALL

Edit: As pointed out in the comment by Vladimir Baranov, there's no need to check if a.IdPed IS NULL on the first SELECT since if it is, the JOIN would return no rows.

SELECT
    a.*,
    c.InvoiceDate
FROM TableA a
INNER JOIN TableC c ON c.IdPed = a.IdPed

UNION ALL

SELECT
    a.*,
    c.InvoiceDate
FROM TableA a
INNER JOIN TableB b ON b.IdOp = a.IdOP
INNER JOIN TableC c ON c.IdPed = b.IdPed
WHERE a.IdPed IS NULL

edited Apr 30 '15 at 00:44

answered Apr 30 '15 at 00:25

Felix Pamittan

31,544
7
41
67

1

Add to your answer explanation that in the first `SELECT` there is no need for the check `WHERE a.IdPed IS NOT NULL`: when `IDPed` is NULL, the `JOIN` would return no rows, so there is no need to explicitly check for it here. In second `SELECT` the check is necessary. – Vladimir Baranov Apr 30 '15 at 00:37
Thanks, but this seems even more complex that my current attemp, joining twice tableC. I'm looking for a simple answer. – ericpap Apr 30 '15 at 01:01
Hmm, I think this would be faster, especially with the proper index. – Felix Pamittan Apr 30 '15 at 01:05
It is not a performance problem. I'm looking for a simple SQL sintaxis. Thanks – ericpap Apr 30 '15 at 01:07

SQL Server Conditional JOIN two tables

2 Answers2