Solved

sql help

Posted on 2014-11-12
8
138 Views
Last Modified: 2014-11-12
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
Comment
Question by:freezingHot
8 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 40437619
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40437623
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40437625
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40437649
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Closing Comment

by:freezingHot
ID: 40437657
thank you - worked perfectly... i appreciate all of the responses.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40437670
> 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
 
LVL 1

Author Comment

by:freezingHot
ID: 40437692
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40437701
Ok.   btw Nice job laying out the sample data and expected set.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now