Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Annoying join problem & frozen brain. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, August 21, 2012 4:09 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, August 30, 2013 6:39 AM Points: 77, Visits: 397
 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!
Post #1347671
 Posted Tuesday, August 21, 2012 4:19 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, August 30, 2013 6:39 AM Points: 77, Visits: 397
 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.:)
Post #1347678
 Posted Tuesday, August 21, 2012 4:23 AM
 SSChasing Mays Group: General Forum Members Last Login: Today @ 7:29 AM Points: 640, Visits: 2,482
 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
Post #1347680
 Posted Tuesday, August 21, 2012 4:24 AM
 SSChasing Mays Group: General Forum Members Last Login: Today @ 7:29 AM Points: 640, Visits: 2,482
 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
Post #1347681
 Posted Tuesday, August 21, 2012 4:27 AM
 Old Hand Group: General Forum Members Last Login: Today @ 2:43 AM Points: 306, Visits: 948
 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`
Post #1347682
 Posted Tuesday, August 21, 2012 4:30 AM
 Old Hand Group: General Forum Members Last Login: Today @ 2:43 AM Points: 306, Visits: 948
 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...
Post #1347683
 Posted Tuesday, August 21, 2012 4:37 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, August 30, 2013 6:39 AM Points: 77, Visits: 397
 Thank you both!! Appreciate you taking the time.:)
Post #1347688
 Posted Tuesday, August 21, 2012 4:41 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:55 AM Points: 6,272, Visits: 12,080
 `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
Post #1347691

 Permissions