How do I retrieve results of this but not that?

Dev Shed Forums Sponsor:

September 20th, 2011, 12:39 PM

Lori J

Registered User

Join Date: Sep 2011

Posts: 8

Time spent in forums: 2 h 48 m 3 sec
Reputation Power: 0

How do I retrieve results of this but not that?

I have a table "WR" that houses client information. I have another table "TR" that house information about services. I have a field "services" in the "TR" table that can have many codes if the client has received those services.

I would like to pull a query that shows me if the client has been in 1, 2, 3, 4 # codes BUT NOT IN 5. Is this possible?
Right now I would use the IN function to get the services I wanted to see, but if that client has received a 5 at any time, I don't want to see the client record at all.

September 20th, 2011, 01:14 PM

Vomster

Contributing User

Join Date: Feb 2005

Posts: 724

Time spent in forums: 1 Week 3 Days 2 h 26 m 31 sec
Reputation Power: 290

Show your query

September 20th, 2011, 01:20 PM

Lori J

Registered User

Join Date: Sep 2011

Posts: 8

Time spent in forums: 2 h 48 m 3 sec
Reputation Power: 0

Quote:

Originally Posted by Vomster

Show your query

select wr.last_name,
wr.first_name,
tr.service,
tr.begin_date
from wr inner join tr on wr.id=tr.id
where tr.service IN ('1','2','3','4')

These shows me all clients who have had a service of 1 2 3 4. Which is great. But If they have had a service 5, I don't want to see any of their records.

September 20th, 2011, 03:24 PM

Vomster

Contributing User

Join Date: Feb 2005

Posts: 724

Time spent in forums: 1 Week 3 Days 2 h 26 m 31 sec
Reputation Power: 290

Code:

SELECT
	wr.last_name,
	wr.first_name,
	tr.service,
	tr.begin_date
FROM wr 
INNER JOIN tr ON wr.id=tr.id
WHERE
	tr.service IN ('1','2','3','4') 
	AND wr.id NOT IN 
		(SELECT id 
			FROM tr 
			WHERE tr.service = '5'
		)

September 20th, 2011, 04:33 PM

Lori J

Registered User

Join Date: Sep 2011

Posts: 8

Time spent in forums: 2 h 48 m 3 sec
Reputation Power: 0

AWESOME! It worked perfectly. And makes complete sense. I have never worked with a subquery before and this helps put a lot of connections in place.

I appreciate the help so much!

September 21st, 2011, 07:37 AM

Vomster

Contributing User

Join Date: Feb 2005

Posts: 724

Time spent in forums: 1 Week 3 Days 2 h 26 m 31 sec
Reputation Power: 290

Great

How do I retrieve results of this but not that?

Developer Shed Advertisers and Affiliates