select rows from a database table with common field in sqlite

Question

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.

Andomar · Accepted Answer · 2013-01-14 13:16:48Z

1

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.

answered Jan 14, 2013 at 13:16

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1391868 Over a year ago

Thank you.Suppose the database was large with its size in gigabytes,containing additional columns,wont the query containing join as you mentioned run slower than the query written by Stefan below?

Andomar Over a year ago

The queries are basically equivalent and the database might even execute them in exactly the same way. Which actually performs better is hard to tell without testing, and performance might even depend on database or even database version.

user1391868 Over a year ago

i have edited my question to a new scenario,am sorry if am not supposed to edit the question in that way.

Andomar Over a year ago

New scenarios belong in a new question I'd think. People should be able to read your questions and its answers without being aware of its history.

Stefan Rasmusson · Accepted Answer · 2013-01-14 13:20:10Z

0

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'))

answered Jan 14, 2013 at 13:20

Stefan Rasmusson

5,6133 gold badges25 silver badges48 bronze badges

Collectives™ on Stack Overflow

select rows from a database table with common field in sqlite

firstname lastname

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

firstname lastname

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related