I have 3 tables Work ( item_id , account_id ) Checked (item_id , account_id , rating) Items ( item_id , item , date_created)
Work is the DB that has an entry for each Item a User has in their queue 1 to 1 with Items. Checked is the table where once a user checks an item its rating goes here 1 to Many I require at least 3 people check an item 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 Can I use Math to build the Having Clauses?? If the item is in Checked 2 times and is in Work 1 Time I don't want the item to be assigned to anyone anymore. Thx for any help been going nuts over this for about 12 hours E ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2897 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
