mardi 5 mai 2015

SQL query JOIN a table on itself (separate IDs and Titles tables)

Order table

o_id   customer_id   drink_id   food_id
----   -----------   --------   -------
 1         22         10          12
 2         22         11          12
 3         22         11          12
 4         44         11          13
 5         22         11          13

Title table

t_id    type      title
----    ----      -----
 1       10        Black Velvet
 2       11        Mojito
 3       12        Rice
 4       13        Meat

How to produce this result:

Given for example customer_id = 22

o_id   drink_id   drink_title   food_id   food_title
----   --------   -----------   -------   ----------
 1        10     Black Velvet     12         Rice
 2        11        Mojito        12         Rice
 3        11        Mojito        12         Rice
 5        11        Mojito        13         Meat

I dont know if the right way to do this:

  1. A nested SELECT query?
  2. Or a JOIN on the order table on itself after a rename? ( if this is the case how should the query look like? )

Any help with this (occasionally including the sample query) would be realy appreciated.

Thanks! :)

Aucun commentaire:

Enregistrer un commentaire