#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep 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.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Show your query
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep Power
    0
    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.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    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'
    		)
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep 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!
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Great

IMN logo majestic logo threadwatch logo seochat tools logo