sql help

Posted on 2014-11-12
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 )


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
Question by:freezingHot
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 40

Expert Comment

ID: 40437619
I would use EXCEPT to build a select SQL statement - example at link below:
LVL 65

Expert Comment

by:Jim Horn
ID: 40437623
Give this a whirl, renaming the obvious
-- names not in name.locations 
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 for 'SQL table join' to get a wompload of pictures that describe JOIN behavior very nicely.
LVL 24

Accepted Solution

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40437649
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


Author Closing Comment

ID: 40437657
thank you - worked perfectly... i appreciate all of the responses.
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?

Author Comment

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.
LVL 65

Expert Comment

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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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