mysql select from a table depending on in which table the data is in
- by user253530
I have 3 tables holding products for a restaurant. Products that reside in the bar, food and ingredients.
I use php and mysql. I have another table that holds information about the orders that have been made so far. There are 2 fields, the most important ones, that hold information about the id of the product and the type (from the bar, from the kitchen or from the ingredients).
I was thinking to write the sql query like below to use either the table for bar products, kitchen or ingredients but it doesn't work. Basically the second table on join must be either "bar", "produse" or "stoc".
SELECT K.nume, COUNT(K.cantitate) as cantitate, SUM(K.pret) as pret, P.nume as NumeProduse FROM `clienti_fideli` as K JOIN if(P.tip,bar,produse) AS P ON K.produs = P.id_prod WHERE K.masa=18 and K.nume LIKE 'livrari-la-domiciliu' GROUP BY NumeProduse
