one to many problems

one to many, data from table1 and summary data from table2

Page 1 of 1

2 Replies - 519 Views - Last Post: 04 December 2009 - 07:01 AM Rate Topic: -----

#1 bonecone  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 14-November 09

one to many problems

Post icon  Posted 03 December 2009 - 07:45 PM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: one to many problems

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: one to many problems

Posted 03 December 2009 - 07:58 PM

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



Was This Post Helpful? 0
  • +
  • -

#3 bonecone  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 14-November 09

Re: one to many problems

Posted 04 December 2009 - 07:01 AM

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;
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1