Too many results with multiple WHERE conditions (sub-queries)

Hey guys,

I'm a bit new to the (slightly) more advanced sides of MYSQL, and I seem to be having an error in my sytax. The full MYSQL query is:

SELECT DISTINCT * 
FROM  `greek_vocabulary` 
WHERE  `greek_vocabulary`.`id` 
IN (

SELECT  `user_greek_flashcards`.`word` 
FROM  `user_greek_flashcards` 
WHERE  `email` =  'alexwgold@gmail.com'
)
AND  `greek_vocabulary`.`id` 
IN (

SELECT  `greek_book_vocab`.`vocabid` 
FROM  `greek_book_vocab` 
WHERE  `book` =  'Matthew'
)
ORDER BY  `greek_vocabulary`.`id` ASC

PHP:

This returns 1,094 results. However, just one of the sub-queries (the first), returns only 1,091 results:

SELECT  `user_greek_flashcards`.`word` 
FROM  `user_greek_flashcards` 
WHERE  `email` =  'alexwgold@gmail.com'

PHP:

I can post the tables and whatnot if needed, but I assume it's something simple in the syntax itself. The basic goal is to only show results from `greek_vocabulary` that also show up (via `id`) in two other tabes (both `user_greek_flashcards` and `greek_book_vocab`. Since they need to be in both, it must be flawed that it's returning more rows than one sub-query does on it's own.

This might be done as well through LEFT JOIN, I think, but I can't even wrap my brain around that yet. If someone converted it I would gladly use it though. Been a long day .

Thanks!
- Alex

 

Should be able to do it with an INNER JOIN which will be much faster that nested queries. Also, I wouldn't use DISTINCT *. Use GROUP BY and whatever specific column you want distinct specific column.

Something like this should work:

SELECT * 
FROM   `greek_vocabulary` 
       INNER JOIN `user_greek_flashcards` 
               ON `greek_vocabulary`.`id` = `user_greek_flashcards`.`word` 
       INNER JOIN `greek_book_vocab` 
               ON `greek_vocabulary`.`id` = `greek_book_vocab`.`vocabid` 
WHERE  `user_greek_flashcards`.`email` = 'alexwgold@gmail.com' 
       AND `greek_vocabulary`.`book` = 'Matthew' 
ORDER  BY `greek_vocabulary`.`id` ASC 

Code (markup):

The part that looks incorrect to me is the `greek_vocabulary`.`id` = `user_greek_flashcards`.`word` which would be the same as your first IN() statement. This would imply that the id column in greek_vocabulary is a corresponding value to the word column in the user_greek_flashcards table.

 

Hey jestep,

Thanks for the response! I actually managed to simplify it on my end (turns out I didn't really even need to reference one of the tables), so I've got it working as thus:

SELECT * FROM   `user_greek_flashcards` 
INNER JOIN `greek_book_vocab` ON `user_greek_flashcards`.`word` = `greek_book_vocab`.`vocabid`
WHERE  `user_greek_flashcards`.`email` = '$email' 
    AND `greek_book_vocab`.`book` = '$bookname'

PHP:

I don't totally understand the syntax, though -- do you know of a good explanation somewhere around the web? I've looked some up but it never really clicked. Namely I'm just trying to grasp the "ON" section .

Thanks again!
-Alex

 

An inner join basically says pull this info from table 1, only where it also appears in the other table. ON tells it what column that the 2 tables have in common.

Here's a good chart to visually explain the difference in JOINS. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

If you look at the inner join, it shows only results that occur in both tables.

 

Ah, okay. Thanks for the help / link!

EDIT: Having just looked through the link, that's a bookmarked page now for sure. If anyone is reading this from Google someday in the future, go check it out .