Only include db fields when another field = x

[Adamhumbug]

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

[kicken]

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.

[Adamhumbug]

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