Jump to content

Recommended Posts

Hi all, i am so sorry at how bad i am at setting the title my posts.

Before we get to my question, i would really appreciate it if someone could let me know how you create a table on here to show results.

The following sql selects all of the items in a menu and lists them out which is great.

SELECT  menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id
FROM ssm_menu a
INNER JOIN
ssm_menu_connection b
on a.menu_id = b.menu_id
INNER JOIN
ssm_menu_items c
on b.menu_item_id = c.menu_item_id
WHERE a.menu_id = 1


menu_name	menu_price	menu_item_name	menu_item_category	menu_item_id	menu_id	
Menu One	22.90	Bruschetta of Italian prosciutto, Fior di latte mo...	Starter	1	1	
Menu One	22.90	Crisp skin chicken breast, stuffed with sage, shal...	Main	7	1	
Menu One	22.90	Tagine of vegetable lightly spiced, on preserved f...	Main	15	1	
Menu One	22.90	Test Desert	Desert	23	1	

The above is the full menu and shows all menu items within the menu.

When they have already made some selections, and submitted a menu, i want to display the quantities next to the name of the item.

I have the following sql which is just an amendment of the above

SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty
FROM ssm_menu a
INNER JOIN
ssm_menu_connection b
on a.menu_id = b.menu_id
INNER JOIN
ssm_menu_items c
on b.menu_item_id = c.menu_item_id
inner join 
ssm_menu_order d
on c.menu_item_id = d.menu_item_id
WHERE a.menu_id = 1


menu_name	menu_price	menu_item_name	menu_item_category	menu_item_id	menu_id	job_id	menu_item_qty	
Menu One	22.90	Bruschetta of Italian prosciutto, Fior di latte mo...	Starter	1	1	26	111	
Menu One	22.90	Bruschetta of Italian prosciutto, Fior di latte mo...	Starter	1	1	27	1	
Menu One	22.90	Crisp skin chicken breast, stuffed with sage, shal...	Main	7	1	26	222	
Menu One	22.90	Tagine of vegetable lightly spiced, on preserved f...	Main	15	1	26	333	
Menu One	22.90	Test Desert	Desert	23	1	26	444	

The issue that i have here is that it is displaying some items twice because they have been selected by another user.

I would like to show the whole menu but only show the qty's that relate to job_id 27.

I hope this makes sense and if further structure is required, i will be happy to provide it.

Kind Regards


Adam

Make your join a LEFT JOIN and add the job id condition as part of the join.

SELECT 
    menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty
FROM ssm_menu a
INNER JOIN ssm_menu_connection b ON a.menu_id = b.menu_id
INNER JOIN ssm_menu_items c ON b.menu_item_id = c.menu_item_id
LEFT JOIN ssm_menu_order d ON 
    c.menu_item_id = d.menu_item_id 
    AND d.job_id=27
WHERE 
    a.menu_id = 1

 

On a side note, consider re-formatting your queries.  Your current style is quite difficult to read, something like what I put above is much easier.

  • Thanks 1
1 hour ago, kicken said:

Make your join a LEFT JOIN and add the job id condition as part of the join.


SELECT 
    menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty
FROM ssm_menu a
INNER JOIN ssm_menu_connection b ON a.menu_id = b.menu_id
INNER JOIN ssm_menu_items c ON b.menu_item_id = c.menu_item_id
LEFT JOIN ssm_menu_order d ON 
    c.menu_item_id = d.menu_item_id 
    AND d.job_id=27
WHERE 
    a.menu_id = 1

 

On a side note, consider re-formatting your queries.  Your current style is quite difficult to read, something like what I put above is much easier.

Perfect, thanks you so much for this - i was banging my head with it.  I will for sure have a rethink about my formatting.

Thanks Again

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.