CODEWITHSUNDEEP
mysql
Igor Morse
Igor Morse

Reputation: 687

MYSQL Inner Join as Multiple Rows Result

I have two tables on my Database: Menu and Dishes. ( I've translated the columns and database name, don't bother if doesn't make sense )

Menu Table

id     date_begins       date_ends      id_dishes_monday  id_dishes_tuesday  id_wednesday              
 1        xxxxx             xxxxx              1                   2                3

Dishes Table

id      date               dynamic_dishes
 1      2016/03/02              BLOB
 2      2016/03/03              BLOB
 3      2016/03/04              BLOB


I wanted to SELECT the id_dishes_monday, id_dishes_tuesday, id_wednesday From Menu Table and retrieve multiple rows from Dishes Table.

I'm made an attempt using this QUERY but it return only one row and I don't know why.

SELECT D.* FROM Menu M INNER JOIN Dishes D WHERE D.id IN (M.id_dishes_monday,M.id_dishes_tuesday,id_dishes_wednesday) ORDER BY M.id DESC LIMIT 1


What this Query Produces in this Example:

 id      date               dynamic_dishes
 1      2016/03/02              BLOB


What I want:

     id      date               dynamic_dishes
     1      2016/03/02              BLOB
     2      2016/03/03              BLOB
     3      2016/03/04              BLOB



Note that the tables datas are just for exemplification, I'd like to know why this Query didn't worked as well the correct Query for it.

Upvotes: 1

Views: 822

Answers (1)

user5992977
user5992977

Reputation:

Because you are limiting your output!

SELECT D.* 
FROM Menu M
INNER JOIN Dishes D
WHERE D.id IN (M.id_dishes_monday,M.id_dishes_tuesday,id_dishes_wednesday)
ORDER BY M.id DESC

I've deleted the LIMIT 1 at the end of your query, which limit the result to the first row

Upvotes: 2

Related Questions