Join multiple tables and group multiple columns to the same alias

[Roland_D]

I've been looking everywhere for the solution but I seem to be missing a little piece of something here..

 

I have 3 tables: pages, categories, menu

 

PAGES

 

ID | title

1 | Home

2 | About

 

CATEGORIES

ID | name

6 | Flowers

9 | Cats

 

MENU

 

ID | pID | cID

1 | 1 | 0

2 | 2 | 0

3 | 0 | 6

4 | 0 | 9

 

What I'm trying to do is this

 

SELECT p.title AS mytitle, c.name AS mytitle, m.ID

FROM menu m

LEFT JOIN pages p ON p.ID=m.pID

LEFT JOIN categories c ON c.ID=m.cID;

 

What I get however is

 

ID | mytitle | mytitle

1 | Home | NULL

2 | About | NULL

3 | NULL | Flowers

4 | NULL | Cats

 

 

What I need is instead

 

ID | mytitle

1 | Home

2 | About

3 | Flowers

4 | Cats

 

 

Thank you guys!

Edited February 12, 2013 by Roland_D

[Barand]

Use a UNION instead of JOIN

[Psycho]

You could also use an IF() statement in the SELECT portion of the query

 

SELECT m.ID, IF(m.pID<>0, p.title, c.name) AS myTitle
FROM menu m
LEFT JOIN pages AS p ON p.ID = m.pID
LEFT JOIN categories AS c ON c.ID = m.cID