Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

sql help

please help me with the following sql scenario.

table 1:  names ( nameid pk int, firstname varchar )
table 2:  name.locations ( nlid pk int, nameid int, locid int )
table 3:  locations ( locid pk int, town varchar )


table 1:
1, joe
2, mary
3, susan

table 2:
1, 1, 50
2, 1, 51
3, 2, 52

table 3:
50, jonestown
51, seattle
52, chicago

i need the following results:
(nameid, locid)


names not in name.locations and names not in name.locations for all locations
1 Solution
lcohanDatabase AnalystCommented:
I would use EXCEPT to build a select SQL statement - example at link below:

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl, renaming the obvious
-- names not in name.locations 
SELECT t1.name
FROM Table1 t1
   LEFT JOIN Table2 t2 ON t1.name_id = t2.name_id
WHERE t2.location_id IS NULL

-- names not in name.locations for all locations
SELECT t3.name_id, t3.location_id,
FROM Table3 t3
   LEFT JOIN Table2 t2 ON t3.name_id = t2.name_id AND t3.location_id = t2.location_id
WHERE t2.location_id IS NULL

Open in new window

SQL Server expert chapmandew has an excellent article called SQL Server: Table Joins Explained! that spells out joins.  Also, search images.google.com for 'SQL table join' to get a wompload of pictures that describe JOIN behavior very nicely.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Here is your exception report:

select names.nameid, locations.locid
from names
CROSS JOIN locations
SELECT nameid, locid
FROM name.locations
ORDER BY nameid, locid

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Vitor MontalvãoMSSQL Senior EngineerCommented:
Here you go:
SELECT n.nameid, l.locid
FROM names n
	CROSS JOIN locations l 
		FROM name_locations nl
		WHERE n.nameid = nl.nameid AND l.locid = nl.locid)
ORDER BY n.nameid, l.locid

Open in new window

freezingHotAuthor Commented:
thank you - worked perfectly... i appreciate all of the responses.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> i appreciate all of the responses.
Curiosity overwlems me .. did you at least try mine, which was not only a working solution but references an existing article and pictoral views of table joins?
freezingHotAuthor Commented:
yes i did - however, your join for t3 fails as it doesn't have the column for name_id.  when i used phillip's response, it worked the first time... therefore, i went with his response.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.   btw Nice job laying out the sample data and expected set.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now