How to join nearly identical several queries into one?
Assume I have an order_dummy
table where order_dummy_id, order_id, user_i开发者_开发知识库d, book_id, author_id
are stored. You may complain the logic of my table but I somehow need to do it that way. I want to execute following queries.
SELECT *
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 1
ORDER BY `order_dummy_id` DESC
LIMIT 1
SELECT *
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 2
ORDER BY `order_dummy_id` DESC
LIMIT 1
SELECT *
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 3
ORDER BY `order_dummy_id` DESC
LIMIT 1
Please keep in mind that several numbers of same book is included in one order. Therefore, I list order_dummy_id
by descending and limit 1
so only LATEST ORDER of A BOOK is shown. But my goal is to show other books in that way in one table. I used group by
like this ...
SELECT *
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
GROUP BY book_id
but it only shows order_dummy_id
with ascending result. I have no idea anymore. Looking forward your kindness help!
To get the results of multiple selects in one go use UNION ALL:
(
SELECT *
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 1
ORDER BY `order_dummy_id` DESC
LIMIT 1
)
UNION ALL
(
SELECT *
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 2
ORDER BY `order_dummy_id` DESC
LIMIT 1
)
UNION ALL
(
SELECT *
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 3
ORDER BY `order_dummy_id` DESC
LIMIT 1
)
You need to normalize the relationship between your tables. book id should not be in order row since we can have multiple books per order. You need to have a table called "ordered items" and store your books there along with ordered quantity - no need for multiple rows.
To answer your question, I can point you to SQL operator IN
:
select * from table where id in (1,2,3)
Also, depending on the context you can use OR operator
select * from table where id = 1 or id = 2 or id =3
精彩评论