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-Enthusiastic Group: General Forum Members Last Login: Wednesday, December 7, 2016 3:31 AM Points: 154, Visits: 610
 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-Enthusiastic Group: General Forum Members Last Login: Wednesday, December 7, 2016 3:31 AM Points: 154, Visits: 610
 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
 Say Hey Kid Group: General Forum Members Last Login: Tuesday, December 6, 2016 4:59 AM Points: 698, Visits: 3,019
 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
 Say Hey Kid Group: General Forum Members Last Login: Tuesday, December 6, 2016 4:59 AM Points: 698, Visits: 3,019
 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
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 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
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 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-Enthusiastic Group: General Forum Members Last Login: Wednesday, December 7, 2016 3:31 AM Points: 154, Visits: 610
 Thank you both!! Appreciate you taking the time.:)
Post #1347688
 Posted Tuesday, August 21, 2012 4:41 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 9:41 AM Points: 8,605, Visits: 18,775
 `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