SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table has multiple rows with equal field

    I have a table where several rows can have one matching field. For example,

    a | 1 | abc
    a | 2 | tre
    a | 7 | q
    b | 4 | g
    c | 3 | w
    c | 5 | g

    How can I create a query that will display field1 which contains two or more field2 specified values. So if I was to, SELECT field1 WHERE field2 = 1 AND field2 = 7 it would output a. Hopefully this isn't too confusing. Thanks in advance!

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow

    Code:
    Select * from `table` where `field2` IN (1,2)
    Is that you meant ?

    Can you show me what results you want from the table pattern?

  3. #3
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm.. I don't think so. Basically, for input 1 or 1, 2 or 1, 2, 3 I want it to return a. If it was

    a | 1 | abc
    a | 2 | tre
    a | 7 | q
    b | 4 | g
    c | 1 | w
    c | 5 | g

    1 should return both a and c while 1, 5 should return only c.

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    u said 1,5 should return c

    so wat abt 5,1 is it same as 1,5 which return c

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT field1 
      FROM daTable
     WHERE field2 IN ( 1,7 ) -- 2 values listed
    GROUP
        BY field1
    HAVING COUNT(*) = 2 -- number of values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT field1 
      FROM daTable
     WHERE field2 IN ( 1,7 ) -- 2 values listed
    GROUP
        BY field1
    HAVING COUNT(*) = 2 -- number of values
    Thanks for the response, but I don't think this is it.
    Basically for

    a | 1

    b | 1
    b | 2

    c | 1
    c | 2
    c | 3

    I would like 1 to return a, b, c; 1, 2 to return a, b; and 1, 2, 3 to return only c. Hopefully this clears it up.

  7. #7
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT field1 
      FROM daTable
     WHERE field2 IN ( 1,7 ) -- 2 values listed
    GROUP
        BY field1
    HAVING COUNT(*) = 2 -- number of values
    Actually, with a very slight modification I was able to make this work. Thank you so much!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dimkasmir View Post
    Actually, with a very slight modification I was able to make this work. Thank you so much!
    what was the modification???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what was the modification???
    Just changed
    Code:
    HAVING COUNT(*) = 2
    to
    Code:
    HAVING COUNT(*) >= 2
    Basically the same thing but this is what I need. Thank you so much for the help, though!

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but >= 2 wouldn't give the results from post #6?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, the point of the comments in the query was that you should adjust the count in the HAVING clause to match the number of values you're looking for
    Code:
    SELECT field1 
      FROM daTable
     WHERE field2 IN ( list of values ) -- N values listed
    GROUP
        BY field1
    HAVING COUNT(*) = N -- number of values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    well, the point of the comments in the query was that you should adjust the count in the HAVING clause to match the number of values you're looking for
    Code:
    SELECT field1 
      FROM daTable
     WHERE field2 IN ( list of values ) -- N values listed
    GROUP
        BY field1
    HAVING COUNT(*) = N -- number of values
    You still do put the number of values, except to fully satisfy my needs it needs to be >= that number rather than =.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dimkasmir View Post
    You still do put the number of values, except to fully satisfy my needs it needs to be >= that number rather than =.
    yes, i understand

    this would be a query to find "at least N" matches

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dimkasmir View Post
    a | 1

    b | 1
    b | 2

    c | 1
    c | 2
    c | 3

    I would like 1 to return a, b, c; 1, 2 to return a, b; and 1, 2, 3 to return only c. Hopefully this clears it up.
    So apparently (1,2) should actually produce (b,c) not (a,b)

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by crmalibu View Post
    So apparently (1,2) should actually produce (b,c) not (a,b)
    according to post #12, yes indeedy

    say, did you like my friendly poke earlier on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for input 1 or 1, 2 or 1, 2, 3 I want it to return a. If it was

    a | 1 | abc
    a | 2 | tre
    a | 7 | q
    b | 4 | g
    c | 1 | w
    c | 5 | g

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    dear predictionbook, who are you?

    do you have a similar question? yours looks different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:


    Quote Originally Posted by r937 View Post

    say, did you like my friendly poke earlier on?
    Oh, that lol.

    I might retort by publicly writing some of my flawed sql querys and announcing I'm a disciple of yours


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
  •