CODEWITHSUNDEEP
sqlsqlite
user1391868
user1391868

Reputation: 45

Select rows from multiple tables with common fields in sqlite

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 ?

Upvotes: 0

Views: 3231

Answers (3)

user1391868
user1391868

Reputation: 45

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)

Upvotes: -1

user1391868
user1391868

Reputation: 45

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)


Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 2

Related Questions