for loop functionality in mysql query

Question

this is MYSQL query question

First, let say we have

[ALL_MENU]

name
-----
pasta
pizza

and people ordered

ordered

customer name status              
john     pasta   delivered        
kim      pasta   delivered       
john     pizza   delivered        
john     pasta   delivered        

I want to go through the ordered table and find anyone who ordered all the menu

In this example, kim only ordered pasta so it should not be included but john ordered both pasta and pizza, so it should be included on the result.

is There any type of query to do the 'For loop' ability?

thank you

(ps, right now I only allow to use

some basic function command from mysql

group by , create view, exists , all, not exist , unique such and such )

=========================================================================

Ok, From the answer the count number of all menu thing is work when (customer,name) are the primary key But what if i added the data column, and primary key as (customer,name,data)

   customer name status              date
        john     pasta   delivered        3/4
        kim      pasta   delivered        3/5
        john     pasta   delivered        3/5

this is the new column i made john ordered the only pasta with 2 different date, but johns number of order count as 2.

so count method will not work for this

How do we fix this problem?

Answer 1

The functionality of a for loop is a cursor. (FYI, most DBMS have such a construct.)

But never use a cursor when a plain old query will do just fine.

Here is one possible solution:

SELECT customer
FROM ordered
GROUP BY customer
HAVING COUNT(DISTINCT name) = (SELECT COUNT(*) FROM all_menu)

(This assumes that all names in ordered are found in all_menus, e.g. there is foreign key. If not, you'll have to add JOIN all_menu ON ordered.name = all_menu.name in the FROM clause.)

EDIT: "Simple"(!) commands only:

SELECT customer
FROM ordered o1
WHERE NOT EXISTS (
    SELECT * FROM all_menu
    WHERE name NOT IN (SELECT name FROM ordered o2 WHERE o1.customer = o2.customer)
)