How to join almost identical multiple queries to one?
Suppose I have a table order_dummy
where are order_dummy_id, order_id, user_id, book_id, author_id
saved. You can complain about the logic of my table, but somehow I need to do this. I want to fulfill the 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 issues of the same book are included in one order. So I am listing order_dummy_id
in descending order and limiting 1
, so only LAST ORDER A BOOK is displayed. But my goal is to show other books in this 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 an upward result. I have no idea. We look forward to your kindness!
a source to share
To get the results of multiple selections 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
)
a source to share
You need to normalize the relationship between your tables. the book ID does not have to be in the order line as we can have multiple books in the order. You need to have a table called "ordered items" and store your books there along with the ordered quantity - no need for multiple lines.
To answer your question, I can point you to an SQL statement IN
:
select * from table where id in (1,2,3)
Also, depending on the context, you can use the OR operator
select * from table where id = 1 or id = 2 or id =3
a source to share