vendredi 1 mai 2015

How to do the proper SQLite query to a table with several foreign keys?

I have a table with two foreign keys pointing to the same table:

 create table person (
     id integer not null,
     living_city integer,
     birth_city integer, 
     foreign key (living_city) references city(id),
     foreign key (birth_city) references city(id)
  )

 create table city (
     id integer not null,
     name varchar
 )

I have added some data so the tables looks like:

 person 
    1   peter   1   2
    2   mary    1   1
    3   ed      2   3

 city
    1   london
    2   paris
    3   rome

The question looks easy but I can find how to do it… I have already searched all google!!

I am looking for a query like the following but getting the foreign keys names, not the numbers(id):

SELECT name, living_city, birth_city FROM person

The desired result is:

  > peter   london  paris
  > mary    london  london
  > ed      paris   rome

Thank you!

Aucun commentaire:

Enregistrer un commentaire