CODEWITHSUNDEEP
sql
user6501335
user6501335

Reputation: 11

SQL Joins using NULL

Table 1

Table 2

I want to join the two tables above to show the appropriate Authorizationcode based on the NameID. If the NameID matches, then will show corresponding Authorizationcode, If it does not, then it will show the other Authorizationcode NameID and OrderID are uniqueidentifiers.

The result should be:

cyclophosphamide - Auth01234
Adriamycin RDF - Auth01234
Neulasta - Auth04567

Not able to join based on NULL NameID. Please suggest.

Upvotes: 1

Views: 56

Answers (4)

Martin Smith
Martin Smith

Reputation: 452977

Your screenshots are from management studio so a SQL Server specific answer.

SELECT t2.Name, CA.AuthorizationCode
FROM Table2 t2
CROSS APPLY 
(
SELECT TOP (1) *
FROM Table1 t1
WHERE t1.OrderId = t2.OrderId
AND (t1.NameId = t2.NameId OR t1.NameId IS NULL)
ORDER BY t1.NameId DESC -- not null prioritised over null
) CA

It would need an index on table1 (OrderId, NameId)

Upvotes: 1

P. Kouvarakis
P. Kouvarakis

Reputation: 1943

Try this one:

SELECT t1.Name, ISNULL(t2.AuthorizationCode, t3.AuthorizationCode) AS AuthorizationCode
FROM Table1 AS t1 
     LEFT JOIN Table2 AS t2 on t1.NameID = t2.NameID AND t1.OrderID = t2.OrderID
     LEFT JOIN Table2 AS t3 on t3.NameID IS NULL  AND t1.OrderID = t3.OrderID

I assume you also want to join on OrderID. If not please make your question more clear.

Upvotes: 1

CR41G14
CR41G14

Reputation: 5594

I would approach this with a UNION

SELECT DISTINCT NameID, AuthorisationCode
FROM 
( SELECT NameID, AutorisationCode
  FROM table1 t1 INNER JOIN
     table2 t2 ON t1.nameid = t2.nameID

  UNION
  SELECT NameID, AutorisationCode
  FROM table1 t1 LEFT OUTER JOIN
     table2 t2 ON t1.nameid = t2.nameID
) DATA

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is a bit tricky. Here is a method that uses two left joins. The condition on the second one only chooses the NULL row from table2 (a cross join could also be used):

select t1.name, coalesce(t2.authorizationcode, t2null.authorizationcode) as authorizationcode
from table1 t1 left join
     table2 t2
     on t1.nameid = t2.nameid left join
     table2 t2null
     on t2null.nameid is null;

This is ANSI standard and should work in most databases.

Upvotes: 1

Related Questions