Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
The question: Display the names of the workers that live in the same cities as their chef.Table one: Werknemercolumnes: wnr (workersnumber), wnaam (workersname), chef (wnr of the chefs)Table two: Privecolumnes: wnr (workersnumber), wnaam (workersname), city (names of the city)What should my OUTER JOIN LOOK LIKE?
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2012-12-02 : 07:03:04
is chef also present as a worker in worker table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
jurE
Starting Member
3 Posts
Posted - 2012-12-02 : 08:27:41
Yes!
quote:Originally posted by visakh16 is chef also present as a worker in worker table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
bandi
Master Smack Fu Yak Hacker
2242 Posts
Posted - 2012-12-03 : 01:48:50
Try this once...........
;with cte AS( SELECT city, w.wnr, w.wnaam, chef FROM @Werknemer w JOIN @Prive p ON w.wnr = p.wnr)SELECT wnaamFROM cte cWHERE city = (SELECT city FROM @Prive p WHERE p.wnr = c.chef)
--Chandu
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2012-12-03 : 07:38:07
quote:Originally posted by jurE Yes!
quote:Originally posted by visakh16 is chef also present as a worker in worker table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
jurE
Starting Member
3 Posts
Posted - 2012-12-04 : 15:48:00
Thanks for all the replies!This was the anwers: SELECT A.wnaam AS chef, B.wnaam AS werknemer FROM Werknemer A INNER JOIN Werknemer B ON (A.wnr = B.chef) INNER JOIN Prive C ON (A.wnr = C.wnr) INNER JOIN Prive D ON (B.wnr = D.wnr AND C.woonplaats = D.woonplaats) ORDER BY chef