Eric Haskins wrote: > So I am trying to generate a query to populate Work from Items. > > Query: > SELECT > Items.item_id, > count( Checked.item_id ) AS checkcount > FROM > Review.Items > LEFT JOIN Review.Checked USING ( term_id ) > LEFT JOIN Review.Work USING ( term_id ) > GROUP BY > item_id > HAVING > checkcount <3 > LIMIT 50 > > This query works but just returns everything in Items that isnt in > Checked more than 3 times. I cant figure out the WHERE clause to not > allow a user to get an item if it is in their queue(Work) already
If it is in their work queue Review.Work.term_id is filled out. If it isn't, it is NULL: SELECT Items.item_id, count( Checked.item_id ) AS checkcount FROM Review.Items LEFT JOIN Review.Checked USING ( term_id ) LEFT JOIN Review.Work USING ( term_id ) WHERE Review.Work.term_id IS NULL GROUP BY item_id HAVING checkcount <3 LIMIT 50 Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2898 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
