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

Discussion in 'MySQL' started by bonecone, Dec 3, 2009.

0
  1. #1
    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?
     
    bonecone, Dec 3, 2009 IP
  2. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #2
    since it is one to many you won't have problem retrieving them.

    SELECT menu_id, menu_title, menu_item_id, menu_item_parent, menu_item_status FROM menus JOIN menu_items ON menu_id = menu_item_parent

    and for counts query will be..

    SELECT menu_id, menu_title, SUM(IF(menu_item_status = 'published', 1, 0)) as Published, SUM(IF(menu_item_status = 'unpublished', 1, 0)) as Unpublished, SUM(IF(menu_item_status = 'deleted', 1, 0)) as Deleted FROM menus JOIN menu_items ON menu_id = menu_item_parent group by menu_id

    hope this solves it...
     
    mastermunj, Dec 3, 2009 IP
  3. bonecone

    bonecone Peon

    Messages:
    54
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Works, thanks! Just had to make it a LEFT JOIN so that all the menus would display and not just the ones that had menu items.
     
    bonecone, Dec 4, 2009 IP