SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Check same column multiple times

    I have two tables structured as below:


    -------------Table1----------------------------Table2-----------------
    -----------------------------------------------------------------------
    |----user_id----|---username--|--||--|--user_id--|-group_id-|--value--|
    ----------------------------------||------------------------------------
    |------10-------|----Johnno---|--||--|----10----|---- 6----|---10----|
    ----------------------------------||------------------------------------
    |------15-------|-----Billy-----|--||--|----10----|-----8----|---15----|
    ----------------------------------||------------------------------------
    |------20-------|-----Sally----|--||--|----15-----|----8----|---16----|
    ----------------------------------------------------------------------


    What would like help with is how to best structure a query that will select user_id and username from Table1 where user_id matches user_id in Table2 and BOTH (group_id = 6 where value = 10) AND (group_id = 8 and value = 15) in Table2 (or even more comparisons)

    If both conditions are not met in table two, then no results are returned.


    Thanks so much for your help!


    Sonia

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ah, I've seen Rudy answer this enough times to try it myself

    Code:
    SELECT
      t1.user_id,
      t1.username 
    FROM t1
    INNER JOIN t2
      ON t1.user_id = t2.user_id
    WHERE 
      (t2.group_id = 6 AND t2.value = 10)
      OR
      (t2.group_id = 8 AND t2.value = 15)
    GROUP BY t1.user_id
    HAVING COUNT(*) = 2
    This assumes that the (group_id, value) pairs are distinct for each user_id.

    Code:
    mysql> select t1.user_id, t1.username from t1 inner join t2 on t1.user_id = t2.user_id where (t2.group_id = 6 and t2.value = 10) or (t2.group_id = 8 and t2.value = 15) group by t1.user_id having count(*)  = 2;
    +---------+----------+
    | user_id | username |
    +---------+----------+
    |      10 | Johnno   |
    +---------+----------+
    1 row in set (0.00 sec)

  3. #3
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Dan,

    Thanks for your reply, however:

    You have used an OR clause whereas what I am looking for is a query that will check that both conditions in Table2 (or more) are true.

    Each individual user can have multiple entries in Table2, but only a single row in Table1


    Sonia

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The query requires two rows are returned before the GROUP collapses them to one (see the last line). It will not return a user with only one matching row in table 2. Try it on your data

  5. #5
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, thanks a bunch, that does indeed work!

    Could you please explain the last line:

    HAVING COUNT( * ) = 2

    Since I must generate the query dynamically and there may be more than 2 conditions for table two, do I need to increment the "2" for however many "OR ( t2.group_id = AND t2.value = )" I have in the query?


    Sonia

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you run this query without the GROUP BY line, you'll get back one row for each of those (group, value) conditions in the query. GROUP BY has the effect of merging multiple rows into one, which I use to collapse each set of rows having the same user_id into a single row in the result set returned.

    HAVING clauses allow you to impose conditions on the sets being collapsed, so HAVING COUNT(*) = 2 means there were two rows for that user_id or no row is returned.

    Yes you will need to change that value to match the number of (group,value) pairs you're going to OR.

  7. #7
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, this doesn't work at all when the query gets more complex. Could you please look at the following real world query which returns results that meet only one of the OR conditions (they must meet all) and suggest a solution:

    SELECT m.member_id, m.member_djname, m.member_firstname, m.member_lastname, m.member_country, cm.club_name FROM member_master m LEFT JOIN club_master cm ON m.member_id = cm.club_member_id INNER JOIN member_genre mg ON m.member_id = mg.genre_member_id WHERE (mg.genre_master_id = '1' AND mg.genre_percent >= '10') OR (mg.genre_master_id = '14' AND mg.genre_percent >= '10') AND m.member_org_id ='1' AND m.member_country IN ("CA") GROUP BY m.member_id HAVING count(*) = 2 ORDER BY m.member_djname


    Thanks!

    Sonia

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here are your conditions --

    WHERE (mg.genre_master_id = '1' AND mg.genre_percent >= '10')
    OR (mg.genre_master_id = '14' AND mg.genre_percent >= '10')
    AND m.member_org_id ='1'
    AND m.member_country IN ("CA")

    this can, for the purposes of logical analysis, be rewritten as

    WHERE a
    OR b
    AND c
    AND d

    in other words, what you have is this --

    WHERE a OR b AND c AND d

    which actually gets executed as though it were written like this --

    WHERE a OR ( b AND c AND d )

    however, i'm quite certain that what you meant is this --

    WHERE ( a OR b ) AND c AND d

    relate this back to your original WHERE clause and see if i'm not right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is actually being returned, since the GROUP BY/HAVING is not working with a more complex query is:

    WHERE ( a OR b ) AND c AND d

    Where logically what we want is

    WHERE ( a AND b ) AND c AND d

    We cant of course use WHERE ( a AND b ) since the columns are the same.

    What Dan posted works just fine for the simple query example, however it doesn't work where the query is more complex with other conditions such as my real world example.

    So essentially what I am looking for is a query that will return results with both:

    (mg.genre_master_id = '1' AND mg.genre_percent >= '10') as true
    (mg.genre_master_id = '14' AND mg.genre_percent >= '10') as true


    Thanks!

    Sonia

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sonia,

    What Rudy is telling you is that you need some more parentheses in that WHERE clause for it to be what you meant it to be. The ANDs and ORs are right already.

  11. #11
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it guys... thanks a bunch!


    SELECT m.member_id, mg.genre_member_id, m.member_djname, m.member_firstname, m.member_lastname, m.member_country, cm.club_name FROM member_master m LEFT JOIN club_master cm ON m.member_id = cm.club_member_id INNER JOIN member_genre mg ON m.member_id = mg.genre_member_id WHERE ( (mg.genre_master_id = '1' AND mg.genre_percent >= '10') OR (mg.genre_master_id = '14' AND mg.genre_percent >= '10') ) AND m.member_org_id ='1' AND m.member_country IN ("CA") GROUP BY mg.genre_member_id HAVING count(*) = 2 ORDER BY m.member_djname


    Sonia


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •