## Annoying join problem & frozen brain.

 Author Message snomadj SSC-Enthusiastic Group: General Forum Members Points: 158 Visits: 616 create table A(a int, b int)create table D (a int)insert into A values (1,1)insert into A values (2,1)insert into A values (1,2)insert into A values (4,2)-- this table has other values.insert into D values (1)insert into D values (2)insert into D values (3)insert into D values (4)-- I want to join A & D based on a values where A.b = 1SELECT * FROM DLEFT OUTER JOIN aON A.a = D.aWHERE A.b = 1-- but have the D values 3 & 4 show up with NULL where the A values should be :-- like this:1 1 12 2 13 null null4 null null-- In reality, it's more complex based on a big IN clause that I'm trying to avoid using twice.SELECT * FROM DLEFT OUTER JOIN aON A.a = D.aWHERE A.b = 1AND D.a IN (1,2,3,4)-- Appreciate any help! snomadj SSC-Enthusiastic Group: General Forum Members Points: 158 Visits: 616 SELECT a2.a, D.aFROM DLEFT JOIN( SELECT * FROM A WHERE A.b = 1) AS a2ON a2.a = D.aWHERE D.a IN (1,2,3)oh dear. i think the act of clarifying in a post sorted my brain out & the above code does what I need. sorry to have bothered!!one of those days. Andy Hyslop Say Hey Kid Group: General Forum Members Points: 706 Visits: 3022 Like this?`SELECT * FROM DLEFT JOIN ( SELECT * FROM A WHERE A.b = 1 ) AS AON D.a = A.B` ==========================================================================================================================A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe Andy Hyslop Say Hey Kid Group: General Forum Members Points: 706 Visits: 3022 Didn't see you had already sorted it! ==========================================================================================================================A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe laurie-789651 SSC-Addicted Group: General Forum Members Points: 424 Visits: 1271 You need to put the condition in the join clause:`SELECT D.a, A.a, A.b FROM DLEFT OUTER JOIN AON A.a = D.a And A.b=1WHERE A.b = 1 or A.b is null` laurie-789651 SSC-Addicted Group: General Forum Members Points: 424 Visits: 1271 In fact you don't need the WHERE clause:`SELECT D.a, A.a, A.b FROM DLEFT OUTER JOIN AON A.a = D.a And A.b=1`...but you've fixed it. Anyway - there are a few ideas there... snomadj SSC-Enthusiastic Group: General Forum Members Points: 158 Visits: 616 Thank you both!! Appreciate you taking the time. ChrisM@Work SSCrazy Eights Group: General Forum Members Points: 8957 Visits: 19016 `SELECT * FROM DLEFT OUTER JOIN a ON A.a = D.aWHERE A.b = 1 -- << this turns the LEFT OUTER JOIN into an INNER JOIN AND D.a IN (1,2,3,4) -- use this instead:SELECT * FROM DLEFT OUTER JOIN a ON A.a = D.a AND A.b = 1WHERE D.a IN (1,2,3,4)`Ah, Laurie beat me to it. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps