[bonecone]

I have a one-to-many relationship between these two tables:

menus
------
menu_id(primary key)
menu_title

menu_items
------------
menu_item_id
menu_item_parent(foreign key)
menu_item status

The menu_item_status field can be set to 'published', 'unpublished' or 'deleted'. I want to retrieve the menu_title from menus, along with three fields containing the number of corresponding published, unpublished & deleted items within menu_items.

How do I do this in just one statement?

[P4L]

Question for clarification, the foreign key on the menu_items table links to the primary key on the menus table. If this is the case, try the following:

Select t1.menu_title,
count(t2.menu_item status)

From menus as t1,
menu_items as t2

where t1.menu_id=t2.menu_item_parent

group by t2.menu_item status

[bonecone]

I Found a solution afterwords.

SELECT menus.*, SUM(IF(menu_items.menu_item_status='published', 1, 0 )) AS published, SUM(IF(menu_items.menu_item_status='unpublished', 1, 0 )) AS unpublished, SUM(IF(menu_items.menu_item_status='deleted', 1, 0 )) AS deleted FROM menus LEFT JOIN menu_items ON menu_id = menu_item_parent GROUP BY menu_id;