MySQL JOIN using three tables

Question

I need to make a query getting values from three tables. So, I'm using this:

SELECT 
menussite.MSS_D1sMenu, menussite.MSS_CdiVertical, 
verticais.VER_D1sVertical, 
GROUP_CONCAT(submenussite.SSS_D1sSubMenuSite) 

FROM menussite 
LEFT JOIN verticais ON verticais.VER_CdiVertical = menussite.MSS_CdiVertical 
LEFT JOIN submenussite ON submenussite.SSS_CdiMenuSite = menussite.MSS_CdiMenuSite 
ORDER BY menussite.MSS_CdiMenuSite ASC

The problem is that some cells are empty and the query just showed the rows that totally matches with the query. What I need to do to query not ignore other results?

UPDATE

Here is a SQL Fiddle: http://sqlfiddle.com/#!2/11eabb/2

@Benm using LEFT OUTER JOIN return the same result than using LEFT JOIN — user3004794
– user3004794, Commented Nov 29, 2013 at 18:18
@BenM Here is the error: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN verticais ON verticais.VER_CdiVertical = menussite.MSS_CdiVertical ' at line 12 0.031 sec — user3004794
– user3004794, Commented Nov 29, 2013 at 18:19

Lukman Munaji · Accepted Answer · 2013-11-29 18:59:39Z

1

try or modified this query

SELECT 
m.menu as a, m.vertical as b, v.vertical as c, s.submenu as d
from menu m, vertical v, submenu s where m.id=v.id and m.id=s.id
ORDER BY m.menu ASC

result

http://sqlfiddle.com/#!2/11eabb/5/0

A     B  C  D            e  
item  1  1  vertical 1   submenu 1
item  2  0  vertical 2   submenu 1

answered Nov 29, 2013 at 18:59

Lukman Munaji

517 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Filipe Silva · Accepted Answer · 2013-11-29 18:48:56Z

0

You are missing a GROUP BY menu.menu

SELECT menu.menu,
       menu.vertical,
       vertical.vertical,
       GROUP_CONCAT(submenu.submenu)    
FROM menu
LEFT JOIN vertical ON vertical.id = menu.vertical     
LEFT JOIN submenu ON submenu.menu = menu.id 
GROUP BY menu.menu
ORDER BY menu.menu ASC

sqlfiddle demo

This will get you the values that have no submenus:

MENU    VERTICAL    GROUP_CONCAT(SUBMENU.SUBMENU)
item 1  1           submenu 1,submenu 1
item 2  0           (null)
item 3  2           (null)
item 4  0           (null)

answered Nov 29, 2013 at 18:48

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

1 Comment

Raymond Nijland Over a year ago

@Filipe Silva it is lucky this query returns the "correct" data for columns menu.vertical and vertical.vertical read dbasquare.com/2012/05/15/…

Collectives™ on Stack Overflow

MySQL JOIN using three tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related