Selecting a mysql row from one table based on select statement in another

Question

I am trying to select a tshirts which the user has not voted on yet (from another table "votes")

"submissions" is the table with the tshirts

"votes" is the table that holds votes

"votes" structure is: ID, TEE, USER (where votes.tee == submissions.id)

This is the mysql statement I am trying:

SELECT 
  submissions.name,
  submissions.id,
  submissions.uploader,
  submissions.image_vote
 FROM 
  submissions,votes 
 WHERE 
  submissions.date_voted IS NOT NULL AND
  submissions.id NOT IN (SELECT tee FROM votes WHERE tee=submissions.id AND user='3')
 LIMIT 1  

Now from what I understand I cannot use "tee=submissions.id" because it is from a query outside of this sub query. So how can I pass the sub query the ID of the tshirt I am checking?

Thank you.

Answer 1

sub-selects have never been a particulary shiny area in mysql. Rather, use an outer join:

SELECT 
  submissions.name,
  submissions.id,
  submissions.uploader,
  submissions.image_vote
 FROM 
  submissions LEFT OUTER JOIN votes ON(votes.tee=submissions.id)
 WHERE 
  submissions.date_voted IS NOT NULL AND votes.tee IS NULL
 LIMIT 1  

the left outer join makes sure that at least one record is produced in the result per applicable record of submissions -- if no corresponding record exists in votes, then the votes fields in the joined record are set to NULL, so the AND votes.whatever IS NULL subclause picks out the joined records where no corresponding record exists in votes.

Answer 2

Did you try this:

SELECT 
  submissions.name,
  submissions.id,
  submissions.uploader,
  submissions.image_vote
 FROM 
  submissions,votes 
 WHERE 
  submissions.date_voted IS NOT NULL AND
  submissions.id NOT IN (SELECT tee FROM votes WHERE user='3')
 LIMIT 1 

Maybe not the most efficient, but as close to your idea as possible.