Mysql – How to create new column with value from other table

join;MySQL

I have a table called 'menus':

 menu_id | sort | page_id | parent_page_id
---------|------|---------|----------------
 1       | 0    | 1       | NULL
 1       | 1    | 2       | 1
 1       | 2    | 3       | 1
 1       | 3    | 4       | 1

And a table called 'pages':

 id | url
----|-----------
 1  | vehicles
 2  | car
 3  | bicycle
 4  | bus

I chose to store the data into separate tables because one page can be included in multiple menus.

Now I want to do a query from which the result is:

 menu_id | page_id | sort | url      | parent_url
---------|---------|------|----------|------------
 1       | 1       | 0    | vehicles | NULL
 1       | 2       | 1    | car      | vehicles
 1       | 3       | 2    | bicycle  | vehicles
 1       | 4       | 3    | bus      | vehicles

The columns menu_id, page_id, sort and url can easily be retrieved using a LEFT JOIN:

SELECT
    menus.menu_id,
    pages.id AS page_id,
    menus.sort,
    pages.url
FROM menus
LEFT JOIN pages
ON menus.page_id = pages.id
ORDER BY sort

But how do I get the column parent_url in the query result?

Best Answer

You will want to JOIN to the pages table a second time to get the parentURL. The first time that you join to pages use the page_id, then the second join will use the parent_page_id:

SELECT
    m.menu_id,
    p1.id AS page_id,
    m.sort,
    p1.url,
    p2.url parentUrl
FROM menus m
LEFT JOIN pages p1
  ON m.page_id = p1.id
LEFT JOIN pages p2
  ON m.parent_page_id = p2.id
ORDER BY m.sort;

See SQL Fiddle with Demo. This gives a result:

| MENU_ID | PAGE_ID | SORT |      URL | PARENTURL |
|---------|---------|------|----------|-----------|
|       1 |       1 |    0 | vehicles |    (null) |
|       1 |       2 |    1 |      car |  vehicles |
|       1 |       3 |    2 |  bicycle |  vehicles |
|       1 |       4 |    3 |      bus |  vehicles |
Related Question