Jump to content

Join multiple tables and group multiple columns to the same alias


Roland_D

Recommended Posts

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!

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.