1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'MySQL' started by Submerged, Jun 18, 2012.

  1. #1
    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
     
    Solved! View solution.
    Submerged, Jun 18, 2012 IP
  2. #2
    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.
     
    jestep, Jun 19, 2012 IP
  3. Submerged

    Submerged Active Member

    Messages:
    132
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #3
    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
     
    Submerged, Jun 19, 2012 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    jestep, Jun 19, 2012 IP
    Submerged likes this.
  5. Submerged

    Submerged Active Member

    Messages:
    132
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #5
    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 :).
     
    Submerged, Jun 19, 2012 IP