Conditional join to two different tables based on

问题:

I have a table that looks like this

dbo.Box
ID SourceID    OverrideQueueID
1      1              NULL
2      1              2
3      2              NULL

I need to figure out a way to say if the OverrideQueueID IS NULL then just do a join from dbo.Box to dbo.Source.ID, otherwise if OverrideQueueID IS NOT NULL join to the dbo.Queue.ID instead. Is this possible to do in one select since it is joining to different tables?

I am trying to do this without introducing a bunch of left joins if at all possible.

回答1:

I hope a union will help you, like given below.

Select Col1,Col2
From  dbo.Box B
Join dbo.Source S On S.Id = b.SourceID
Where B.OverrideQueueID  is Null
Union
Select Col1,Col2
From  dbo.Box B
Join  dbo.Queue Q On Q.Id = b.SourceID
Where B.OverrideQueueID  is Not Null

回答2:

One possible way:

select * from Box as a
join Box as b ON a.OverrideQueueID is null and a.ID = b.SourceID
join Queue as q ON a.OverrideQueueID is not null and a.ID = q.ID

回答3:

Try this. You can modify the WHERE clause to fit your needs.

SELECT 
    b.*,
    s.*,
    q.*
FROM
    dbo.Box b
    LEFT JOIN dbo.[Source] s ON s.ID = b.SourceID AND b.OverrideQueueID IS NULL
    LEFT JOIN dbo.[Queue] q ON q.ID = b.OverrideQueueID AND b.OverrideQueueID IS NOT NULL
WHERE
    s.ID IS NOT NULL OR q.ID IS NOT NULL

回答4:

select *,a. form box b 
inner join (select OverrideQueueID ,SourceID
from box where OverrideQueueID  is null)a 
on b.id=a.SourceID

inner join  dbo.Queue.ID a
where b.OverrideQueqeID  is not null
on B.ID = Q.ID

回答5:

You have two records for SourceID = 1.. One as NULL, another as the override. Which one wins, I would assume #2 ID, but could there be an even higher number that should take precidence?

Since you are probably concerned with the per source, it should be as simple as a max() and no joins at all... something like

select 
      b.SourceID,
      max( coalesce( b.OverrideQueueID, b.ID )) as FinalQueue
   from
      Box b
   group by
      b.SourceID

The coalesce as applied will basically take any of the override queues and just replace them with its own source. So, for records 1 and 3 will point back to themselves as queue 1 and 3 to be applied. Only on sourceID = 1 where there are two records will the #2 queue supersede the #1 (NULL override) queue.

dbo.Box
ID SourceID    OverrideQueueID   Coalesce value
1      1              NULL       1
2      1              2          2
3      2              NULL       3

So, the max for sourceID = 1 will be queue 2 and for SourceID = 2 will be queue 3.