mysql joins with a master table

Question

I have a table named menu with columns

menu_id,menu_name,system_id,filename

another table with the menu_users with fields

menu_user_id,menu_id

Another table named system

system_id,system_name

I want to join all the three table to get columns from the menutable and systemname from the system table

I tried

SELECT * FROM menu, menu_users, System 
WHERE menu.menu_id=menu_users.menu_id 
AND menu.system_id=System.system_id

But I'm not able to get the desired result.

Please help me

Answer 1

In order to join, it's better to use a JOIN command:

SELECT * 
FROM menu
INNER JOIN menu_users ON menu.menu_id=menu_users.menu_id
INNER JOIN System ON menu.system_id=System.system_id

That being said, you don't need backticks like you had.

Last but not least, DON'T use SELECT * in any query. Ask for the columns you need only.

Answer 2

You will need to join the table first and then specify on what condition.

Something like:

select * from table_1 INNER JOIN table_2 INNER JOIN table_3 where table_1.ID = table_2.ID AND table_2.ID = table_3.ID

Hope this helps!