[RESOLVED] Query Question

[ehask71]

Ok I have to get some info from my db

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.

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

[tomhath]

Try NOT EXISTS (if I understand your question):

SELECT 
   Items.item_id, 
   count( Checked.item_id ) AS checkcount
FROM 
   Review.Items
   LEFT JOIN Review.Checked USING ( item_id ) 
WHERE NOT EXISTS (
    SELECT 1 FROM Review.Work 
    WHERE Review.Work.item_id = Review.Items.item_id
  )
GROUP BY 
   item_id
HAVING 
   checkcount <3
LIMIT 50