CODEWITHSUNDEEP
sqlmysqldatabase
ahmed
ahmed

Reputation: 14666

MYSQL select "all" of the values rather than "any" of them?

this SQL statement 

"select firstname,lastname from users where lastname in('foo','bar') group by firstname"

it means select all records in table "users" where lastname match any of the "foo" or "bar"

I don't want this behaviour

what I want to retrieve is all records in "users" table where lastnames match all of "foo" and "bar"

so is there anything to do that?

I knew I should replace in('foo','bar') with something but I don't know that thing!?

Thanks

update

for example if the table has these records 

        firstname | lastname
        user1       foo
        user1       bar
        user2       foo

the SQL query should retrieve user1 but not user2 since user2 has 'foo' but does not have 'bar'

Upvotes: 3

Views: 5269

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146469

There are NO records where the lastname matches both "foo" AND "bar" How can a value be simultaneously equal to two different values? It's got to be either one, or the other, or neither.

If you really want all the rows where lastname is equal to "foo", PLUS all the (other) rows where lastname is equal to "bar", then what you have already, using

 In('foo', 'bar')

is correct, although your use of the 'group by firstname' clause will fail because you are not using an aggregate function on the lastName column

Based on Edited question, you want all users which have lastname 'foo' or 'bar' and where there is another user with same first name and the other lastname. 

Select firstName, LastName
From users u
Where lastName In ('foo', 'bar')
  And Exists (Select * From users
              Where firstName = u.firstName
                 And lastName = 
                    Case u.LastName 
                        When 'foo' Then 'bar' 
                        Else 'foo' End)

Upvotes: 3

Philip Davis
Philip Davis

Reputation: 276

SELECT U1.firstname
FROM Users U1, Users U2
WHERE U1.lastname = 'foo'
    AND U2.lastname = 'bar'
    AND U1.firstname = U2.firstname

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89661

Try:

select firstname
    ,lastname
from users
where lastname in('foo','bar')
group by firstname
HAVING count(*) = 2

(works if not allowed to have multiple foos or bars on a firstname)

or

SELECT u1.firstname
FROM users u1
INNER JOIN users u2
    ON u1.firstname = u2.firstname
    AND u1.lastname = 'foo'
    AND u2.lastname = 'bar'
GROUP BY u1.firstname

Upvotes: 5

Related Questions