#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0

    Post Selecting A Distinct Columns


    Col1 : col2:
    ........:.......:.
    5 : 9 :
    ........:.........
    6 : 3 :
    ........:.......:..
    11 : 9 :
    ........:.......:..
    5 : 9 :
    ........:.......:..
    11 : 8 :
    ........:.......:.

    I want to SELECT col2 WHERE 5 and 11 are common in col1

    Which suppose to return :9,9,9

    I will appreciate a statement that will do that .thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0

    How can I select a common rows in a table


    I need a help in this assignment

    Col1 : col2:
    ........:.......:.
    5 : 9 :
    ........:.........
    6 : 3 :
    ........:.......:..
    11 : 9 :
    ........:.......:..
    5 : 9 :
    ........:.......:..
    11 : 8 :
    ........:.......:.

    I want to SELECT col2 WHERE 5 and. 11, in col1 are common in col2.
    Which suppose to return :9,9,9
    I will appreciate a statement that will do that .thanks
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by okesimojs
    ... WHERE 5 and 11 are common in col1
    not sure i understand what you mean by "common"

    does this work for you? --
    Code:
    SELECT col2
      FROM daTable
    INNER
      JOIN ( SELECT col1
               FROM daTable
              WHERE col1 IN ( 5 , 11 )  
             GROUP
                 BY col1
             HAVING COUNT(DISTINCT col1) > 1 ) AS criteria
        ON criteria.col1 = daTable.col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Now I'm confused. The original message appears to be 'unedited' and says 'common in col2'
    !?!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0
    [QUOTE=cafelatte]Now I'm confused.
    Thanks for your concern sir!
    I was using my phone to answer . That's why I couldn't noticed the edited reply .
    Sir, the script you gave me did not return anything .
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by okesimojs
    Sir, the script you gave me did not return anything .
    sorry, remove the DISTINCT

    results:

    9
    9
    9
    8

    still not sure if i understand your requirements, because my query returns the 8 as well, since col1=11 occurs more than once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    I believe they want, where col1 is 5 or 11, all the values of col2 where there are duplicate col2 values.

    Not tested!!!

    Code:
    SELECT col2
      FROM daTable
       WHERE col1 in (5, 11)
       GROUP BY col2
       HAVING COUNT(col2) > 1

    Comments on this post

    • cafelatte agrees : Indeed!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    Almost right! Now with added testing!

    Code:
    select group_concat(col2)
      from daTable
        where col1 in (5,11)
      group by col2
      having count(col2)>1;
    
    +--------------------+
    | group_concat(col2) |
    +--------------------+
    | 9,9,9              |
    +--------------------+
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo