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

    Join Date
    Sep 2013
    Posts
    2
    Rep Power
    0

    Output based on multiple matches in column


    I have joined 2 tables and able to output the proper rows but I want to filter it a bit more.
    Basically outputting rows on multiple matches in the column.

    If the table looks like this
    c1 / c2
    555 / black
    555 / green
    555 / white
    555 / orange
    555 / blue
    222 / white
    222 / blue

    I would like the result showing this

    555 / black
    555/ blue

    They both must be present to output columns 1 and 2.
    Im playing around with "where exists" but not able to get the desired output.
    Appreciate your help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    I may be missing something but I can see no correlation between your two output examples and the expression 'multiple matches' - for example, 'black' only appears once in the first output example.
    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
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select c1,c2
      from t
     where c2 in ('black','blue')
     group
        by c1
    having count(distinct c2) = 2
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    2
    Rep Power
    0
    Hi, i apologize for not being clear, yes black shows once but also shows the other color blue, only if both of these are present the output should show both columns. In this case the id 555 has both colors so it outputs that.
    222 only has blue but no black.

    The code by swampboogie is working. Thank you very much.

IMN logo majestic logo threadwatch logo seochat tools logo