CODEWITHSUNDEEP
sqlsql-servert-sqljoinleft-join
max
max

Reputation: 167

If join returns null then join on default row

I am trying to join 2 tables on a row, and if that row is null, then join that row to the default row.

Table 1: Events

EventID     EventName
----------- -------------
1           January
2           February
3           March
4           April

Table 2: Menus

MenuID     EventID     MenuVersion     
---------- ----------- --------------- 
1                      1
2          3           2
3          4           4
4                      4

What I have Tried

SELECT * FROM Events
LEFT JOIN Menus
ON Events.EventID = Menus.EventID

Output I'm Getting

EventID     EventName     MenuID    EventID    MenuVersion
----------- ------------- --------- ---------- ---------------
1           January                           
2           February                          
3           March         2         3          2
4           April         3         4          4

The default row of the Menus table in this case is the row with the highest MenuID and a null EventID.

Output I want

EventID     EventName     MenuID    EventID    MenuVersion
----------- ------------- --------- ---------- ---------------
1           January       4                    4
2           February      4                    4
3           March         2         3          2
4           April         3         4          4

Upvotes: 1

Views: 237

Answers (1)

Dale K
Dale K

Reputation: 27289

Cross apply the default row and use its values when no row is left joined on.

DECLARE @Events TABLE (EventId INT, EventName VARCHAR(12));
DECLARE @Menus TABLE (MenuId INT, EventId INT, MenuVersion INT);

INSERT INTO @Events (EventId, EventName)
VALUES
(1, 'January'),
(2, 'February'),
(3, 'March'),
(4, 'April');

INSERT INTO @Menus (MenuId, EventId, MenuVersion)
VALUES
(1, null, 1),
(2, 3, 2),
(3, 4, 4),
(4, null, 4);

SELECT E.EventId, E.EventName, COALESCE(M.MenuId, D.MenuId) MenuId, M.EventId, COALESCE(M.MenuVersion, D.MenuVersion) MenuVersion
FROM @Events E
LEFT JOIN @Menus M ON M.EventID = E.EventID
CROSS APPLY (SELECT TOP 1 * FROM @Menus WHERE EventId IS NULL ORDER BY MenuId DESC) D;

Returns as requested:

EventId EventName   MenuId  EventId MenuVersion
1       January     4       NULL    4
2       February    4       NULL    4
3       March       2       3       2
4       April       3       4       4

Note: If you set out your questions like this in future with the DDL/DML statements you'll get a much faster response because it saves people from having to type it all in.

Upvotes: 3

Related Questions