CODEWITHSUNDEEP
sqlsql-server
dislick
dislick

Reputation: 677

Selecting all rows where one field contains the same value

I would like to get the following data out of a table like this:

Example Table

Select all rows where User = "Ashley" or User = "Barney" and SomeID is equal on both rows. I basically need to get all customers where Ashley and Barney are listed as salesmen but not only Ashley or only Barney. Both of them.

I have no idea what value SomeID contains! The only input I have available is "Ashley" and "Barney".

Is there a way to solve this in (Microsoft) SQL? I have tried working with GROUP BY but it does not really help me.

Upvotes: 0

Views: 76

Answers (2)

mucio
mucio

Reputation: 7119

I would do it in this way:

SELECT * 
  FROM MyTable
 WHERE SomeID IN (
                   SELECT SomeID
                     FROM MyTable
                    WHERE Salesman IN ('Ashley','Barney')
                 GROUP BY SomeID
                   HAVING count(distinct Salesman) = 2
                 )

Here a demo in SQLFiddle.

Upvotes: 2

lc.
lc.

Reputation: 116438

To get the IDs, you can use a having-clause with a conditional count for each Salesman value:

select SomeID
from MyTable
group by SomeID
having count(case when Salesman = 'Ashley' then 1 else null end) > 0
and count(case when Salesman = 'Barney' then 1 else null end) > 0

Selecting the entire row is then as easy as joining on this query:

select MyTable.Customer, MyTable.Salesman, MyTable.SomeID
from MyTable
join (
    select SomeID
    from MyTable
    group by SomeID
    having count(case when Salesman = 'Ashley' then 1 else null end) > 0
    and count(case when Salesman = 'Barney' then 1 else null end) > 0
) x on MyTable.SomeID = x.SomeID

Upvotes: 2

Related Questions