Select rows from multiple tables with common fields in sqlite

Question

I have a table as follows:

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

id firstname lastname

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

Now I have another table create table table2(id integer,position integer);

id position

===========
1 5
1 9
2 6
3 7
6 2

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.
Now I also want the position of ben to be one lesser than that of rob, hence the result would be :

id firstname lastname position

===========================
1 ben taylor 5
2 rob taylor 6

What should the sql query be ?

Gordon Linoff · Accepted Answer · 2013-01-14 19:44:35Z

2

To get the last name:

select lastname
from names n join
     position p
     on n.id = p.id
where firstname in ('ben', 'rob')
group by lastname
having count(distinct firstname) = 2 and
       1+max(case when firstname = 'ben' then p.position end) = max(case when firstname = 'rob' then p.position end)

You can then get the original list with:

select n.*, p.position
from names n join
     position p
     on n.id = p.id
where firstname in ('ben', 'rob') and
      lastname in (select lastname
                   from names n join
                        position p
                        on n.id = p.id
                   where firstname in ('ben', 'rob')
                   group by lastname
                   having count(distinct firstname) = 2 and
                          1+max(case when firstname = 'ben' then p.position end) = max(case when firstname = 'rob' then p.position end)
                  )

I think the following query answers your question, with the caveat that this combines the names into one row:

select nben.*, p.position, nrob.*, prob.position
from names nben join
     positions p
     on nben.id = p.id and
        nben.firstname = 'ben' join
     names nrob
     on nrob.firstname = 'rob' and
        nrob.lastname = nben.lastname join
     positions prob
     on nrob.id = prob.id and
        p.position = prob.position - 1

Also, this is untested.

edited Jan 14, 2013 at 19:44

answered Jan 14, 2013 at 17:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

8 Comments

CL. Over a year ago

This query outputs all three taylors, and the smiths.

user1391868 Over a year ago

the result i am looking for is written at the end of the question.

Gordon Linoff Over a year ago

Sorry about that . . . I totally missed the condition on positions. I put in one fix for that, assuming there is only one position per name.

CL. Over a year ago

This doesn't output anything.

user1391868 Over a year ago

there can be many positions per name as in example ben taylor is at position 5 and 9,but ben taylor at position 5 is in the result

|

user1391868 · Accepted Answer · 2013-01-15 04:25:58Z

This gives me the result i needed obtained from Gordon Linoff's solution:
select * from(select n.*, p.position
from names n join
position p
on n.id = p.id
where firstname in ('ben', 'rob') and
lastname in (select lastname
from names n join
position p
on n.id = p.id
where firstname in ('ben', 'rob')
group by lastname
having count(distinct firstname) = 2

              )
         )

as x,names,position
where
(x.id!=names.id and names.id=position.id and names.lastname=x.lastname
and (x.firstname='rob' and names.firstname='ben') and x.position=position.position+1)
or
(x.id!=names.id and names.id=position.id and names.lastname=x.lastname
and (x.firstname='ben' and names.firstname='rob') and x.position+1=position.position)

user1391868 · Accepted Answer · 2013-01-15 04:49:47Z

-1

Gordon Linoff's solutions:
select nben.*, p.position
from names nben join
positions p
on nben.id = p.id and
nben.firstname = 'ben' join
names nrob
on nrob.firstname = 'rob' and
nrob.lastname = nben.lastname join
positions prob
on nrob.id = prob.id and
p.position = prob.position - 1

gives same result as:

select * from(select n.*, p.position
from names n join
position p
on n.id = p.id
where firstname in ('ben', 'rob') and
lastname in (select lastname
from names n join
position p
on n.id = p.id
where firstname in ('ben', 'rob')
group by lastname
having count(distinct firstname) = 2

          )
     )

as x,names,position
where (x.id!=names.id and names.id=position.id
and names.lastname=x.lastname
and (x.firstname='ben' and names.firstname='rob')
and x.position+1=position.position)

answered Jan 15, 2013 at 4:49

user1391868

451 silver badge8 bronze badges

1 Comment

user1391868 Over a year ago

In the two of Gordon Linoff's solutions i edited, which one is more efficient and suitable if the size of the tables are big with size in several gigabytes?

Collectives™ on Stack Overflow

Select rows from multiple tables with common fields in sqlite

id firstname lastname

id position

id firstname lastname position

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

id firstname lastname

id position

id firstname lastname position

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related