self join - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

self join

Author

Topic

copelandtml
Starting Member

5 Posts

Posted - 2011-01-20 : 12:27:32

I'm trying to do a self join to figure out which customers are located in the same state. If there are no other customers, then i don't want to list the information. Where there are other customers, I want to list the Last Name, First Name and State, in
order of State.

Any ideas? I've been playing around with it for a while just can't seem to come up with the right syntax.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-01-20 : 12:28:45

Could you please post your sample data and expected results to help us help you?

Some days you're the dog, and some days you're the fire hydrant.

copelandtml
Starting Member

5 Posts

Posted - 2011-01-20 : 13:23:45

I have a customer table with the following columns:

CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFFERRED

and I want to know which customers live in the same state, I need the final printout to have the lastname, firstname and state.

SELECT c.firstname, c.lastname, r.state
FROM customers c, customers r
//WHERE (not sure what to put here) only return states that have more then one customer in them
ORDER BY state;

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-01-20 : 14:33:15

You could do something like this:

SELECT c.firstname, c.lastname, c.state
FROM customers c
JOIN (SELECT state
	FROM customers
	GROUP BY state
	HAVING count(state) > 1) a
ON c.state = a.state
ORDER BY c.state

Some days you're the dog, and some days you're the fire hydrant.

copelandtml
Starting Member

5 Posts

Posted - 2011-01-20 : 19:17:51

Thanks, that seemed to work just fine. Another quick question how would you go about retrieving something that is not null. I only want the columns to come back that have something in them.

something like this:
SELECT customer#, category, state, orderdate
FROM customers, orders, books
WHERE category ='COMPUTERS' AND orderdate IS NOT NULL;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources