SQL Query - FULL JOIN multiple tables but return NULL results

Question

SQL Query - FULL JOIN multiple tables but return NULL results

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101207 More actions · Answer 1

That is normal behavior for a FULL JOIN. You need to adjust the final SELECT to reflect the fact that any table's results could be NULL, like so:

...
)
SELECT DISTINCT 
    ...
    COALESCE(BAR.[Item No_], ILETR.[Item No_], ILEMS.[Item No_]) AS [Item No_],
    ...
    COALESCE(BAR.[Variant Code], ILETR.[Variant Code], ILEMS.[Variant Code]) AS [Variant Code]
    ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".