Inner join returns repeated registries

Question

well, I'm very new at this, so here is my problem.. I'm trying to make some sort of multiple restaurant menu database, and after so much struggle with the structure, I got one.

this is the overall structure.

Clients
ID (int 10, PK)
mombre(varchar)
etc etc.(etc)

menucat
menuID (int 10, FK references clients.ID)
IDcat  (int 10, PK)
nombre

menuitem
ID (int 10, PK)
catID (int 10, FK references IDcat)
nombre



problem:

i have this query so far:

SELECT c.ID
     , c.nombre
     , mc.nombre
     , mi.nombre
FROM clientes as c
     , menuitem as mi
     , menucat
  JOIN menucat as mc 
    ON mc.IDcat = mi.cat Id
 GROUP 
    by mc.IDcat

this brings me: mc.nombre and mi,nombre fine, as intended.

BUT, c.ID and c.nombre repited as if it where only one registry... how can i formulate this query so it brings my two clients with their respective menus?

sorry for my poor english and my poor skill to write here.

thanks. btw I'm using phpmyadmin SQL console

Answer 1

SELECT c.ID , c.nombre , m.nombre , i.nombre 
FROM Clients AS c 
INNER JOIN menucat AS m 
  ON m.menuID = c.ID 
INNER JOIN menuitem AS i 
  ON i.catID = m.IDcat

This will return ALL menu items with their associated menus and clients. (Assuming they are associated)

Consider renaming the columns in your table to make the references in your database easier. For instance the FK constraint on menucat that references clients.ID should be on a column called ClientID or something similar. Not on one called menuID since it is relating to a client, not a menu.

It would also help if you gave us an example of what you want to return.