Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT DISTINCT Person.ID, ...
- FROM Person LEFT JOIN Manager
- ON (Manager.ID = Person.ID OR Manager.ID = -1))
- WHERE (...)
- id name
- 1 Person One
- 2 Person Two
- 3 Person Three
- 4 Person Four
- 5 Person Five
- id name
- -1 Admin
- 2 Manager One
- 3 Manager Two
- SELECT DISTINCT *
- FROM Person LEFT JOIN Manager
- ON (Manager.id = Person.id OR Manager.id = -1)
- Person One -1 Admin
- Person Two -1 Admin
- Person Two 2 Manager One
- Person Three -1 Admin
- Person Three 3 Manager Two
- Person Four -1 Admin
- Person Five -1 Admin
- insert into #test (test1id, test)
- select 1, 'Judy'
- union all
- select 2, 'Sam'
- union all
- select 3, 'Nathan'
- insert into #test2 (test2id, test1id, test2)
- select 1,1,'hello'
- union all
- select 2,1,'goodbye'
- union all
- select 3,2,'hello'
- select * from #test t
- left join #test2 t2 on t.test1id = t2.test1id
- where test2 = 'goodbye'
- --result set
- --test1id test test2id test1id test2
- --1 Judy 2 1 goodbye
- select * from #test t
- left join #test2 t2 on t.test1id = t2.test1id
- and test2 = 'goodbye'
- --result set
- --test1id test test2id test1id test2
- --1 Judy 2 1 goodbye
- --2 Sam NULL NULL NULL
- --3 Nathan NULL NULL NULL
- select * from #test t
- left join #test2 t2 on t.test1id = t2.test1id
- where test2id is null
- --result set
- --test1id test test2id test1id test2
- --3 Nathan NULL NULL NULL
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement