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

x
  • 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 )

values:

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)

1,52
2,50
2,51
3,50
3,51
3,52

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

http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/
0
 
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.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Here is your exception report:

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

Open in new window

0
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 
WHERE NOT EXISTS (SELECT 1 
		FROM name_locations nl
		WHERE n.nameid = nl.nameid AND l.locid = nl.locid)
ORDER BY n.nameid, l.locid

Open in new window

0
 
freezingHotAuthor Commented:
thank you - worked perfectly... i appreciate all of the responses.
0
 
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?
0
 
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.   btw Nice job laying out the sample data and expected set.
0

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