CODEWITHSUNDEEP
sqlsqlite
user1391868
user1391868

Reputation: 45

select rows from a database table with common field in sqlite

i have a table as follows:

create table table1(id integer,firstname text,lastname text);

firstname lastname

======== =========
1 ben taylor
2 rob taylor
3 rob smith
4 rob zombie
5 peter smith
6 ben smith
7 peter taylor

I want to select rows with a lastname , where the lastname must be shared by ben and rob and firstnames must be ben and rob.
Hence in the above table the result of the select query must be:
1 ben taylor
2 rob taylor
3 rob smith
6 ben smith

what must be the sql query to get the above results?
I tried - select * from table1 as a,table1 as b where a.firstname='ben' and b.firstname='rob' and a.lastname=b.lastname this joined all the resultant rows which is not what i inteneded.

Upvotes: 1

Views: 258

Answers (2)

Stefan Rasmusson
Stefan Rasmusson

Reputation: 5595

select * 
from table1 where first_name = 'ben' or first_name = 'rob' 
and last_name 
in (select last_name from table1 where first_name = 'rob' and last_name 
in (select last_name from table1 where first_name = 'ben'))

Upvotes: 0

Andomar
Andomar

Reputation: 238068

You can use two filtering joins to demand that the lastname is shared with both a Ben and a Rob:

select  *
from    Table1 t1
join    Table1 rob
on      rob.firstname = 'rob'
        and t1.lastname = rob.lastname
join    Table1 ben
on      ben.firstname = 'ben'
        and t1.lastname = ben.lastname
where   t1.firstname in ('ben', 'rob')

Live example at SQL Fiddle.

Upvotes: 1

Related Questions