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

    Join Date
    Nov 2000
    Posts
    35
    Rep Power
    14

    Query using "NOT IN"?


    Hello,

    I'm trying to write a query that will generate a list from table 'fitschedule' that does not contain the value for resid present in table 'fitreserve'.

    Here's the start query I'm working from. I tried incorporating "NOT IN" but I can't get it to work.

    Code:
    $sql = "SELECT id, resid, fdate, ftime, prepost, semester, year FROM 
    			fitschedule
    			WHERE 
    			prepost = 'post'
    			AND
    			semester = 'summer2'
    			AND
    			year = '2014'
    
    			";

    Code:
    SQL query: SELECT * FROM `fitreserve` LIMIT 0, 30 ; 
    Rows: 1
    
    id	resid	email	studid
    1	5	mousel@defend.net	12341234

    Code:
    SQL query: SELECT * FROM `fitschedule` LIMIT 0, 30 ; 
    Rows: 8
    
    id	resid	fdate	ftime	prepost	semester	increments	year
    1	1	2013-06-12	10:00:00	pre	summer1	25	2013
    2	2	2013-06-12	10:25:00	pre	summer1	25	2013
    3	3	2013-06-12	10:50:00	pre	summer1	25	2013
    4	4	2013-06-12	11:15:00	pre	summer1	25	2013
    5	5	2013-06-12	13:00:00	pre	summer1	25	2013
    6	6	2013-06-12	13:25:00	pre	summer1	25	2013
    7	7	2013-06-12	13:50:00	pre	summer1	25	2013
    8	8	2013-06-12	14:15:00	pre	summer1	25	2013
    Any help will be much appreciated!

    Tim
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Code:
    SELECT fitschedule.id
         , fitschedule.resid
         , fitschedule.fdate
         , fitschedule.ftime
      FROM fitschedule
    LEFT OUTER
      JOIN fitreserve
        ON fitreserve.resid = fitschedule.resid
     WHERE fitschedule.prepost = 'post'
       AND fitschedule.semester = 'summer2'
       AND fitschedule.year = '2014'
       AND fitreserve.resid IS NULL

    Comments on this post

    • Bigtime agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2000
    Posts
    35
    Rep Power
    14
    Thank you so much! It would have taken me a long time to come up with that one!

IMN logo majestic logo threadwatch logo seochat tools logo