Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
SELECT ... menus.url AS parent_url ...? –  Nick Chammas Oct 24 '13 at 18:14
add comment

1 Answer

up vote 1 down vote accepted

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 |
share|improve this answer
    
+1 for getting to this first –  RolandoMySQLDBA Oct 24 '13 at 15:33
    
Thank you so much! I'm still struggling with joints, this is another lesson for me :) –  Stan Oct 24 '13 at 17:41
1  
Lesson #2: Doctors can help you with JOINTS. You come here for help with JOINs. –  RolandoMySQLDBA Oct 24 '13 at 18:17
    
Haha of course, I'll stick with joins for now ;) @bluefeet Could you elaborate the syntax, please? Why is p2.url not followed by AS, like p1.id? And, are m, p1 and p2 MySQL variables of some sort? –  Stan Oct 24 '13 at 19:26
    
@Stan The AS is not required when giving an alias. The m, p1 and p2 are table aliases which allows you to reference the table using that instead of the full table name. –  bluefeet Oct 24 '13 at 19:30
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.