Return two identical rows in MySQL

I have an order system that can have several receipts associated with one order. I recently ran into a query that caused an unwanted result.

SELECT info FROM orders WHERE id IN (1, 2, 2) ORDER BY FIELD (id, 1, 2, 2); 

Is there a way to return the line for order number 2 twice? At the moment, the query returns row 1, and then row 2, as expected; however, in this particular case, return to line # 2 is required.

The tables look something like this (I know that MySQL is completely invalid, just for illustration):

 CREATE TABLE orders ( id int(), info VARCHAR(), ) CREATE TABLE links ( orderid int(), receiptid int() ) CREATE TABLE receipts ( id int(), otherinfo VARCHAR(), ) 
+4
source share
3 answers

If I understand the situation correctly, you have two entries in the order table but orderId 2 is listed twice in the link table. If this is correct, then you want:

 select o.info from orders o inner join links l on o.id = l.orderid 
+4
source

If you need to return the string twice, then filtering in the where clause is not what you want. You can do this by filtering with join :

 SELECT o.info FROM orders o join (select 1 as id union all select 2 union all select 2 ) ids on o.id = ids.id ORDER BY FIELD (o.id, 1, 2, 2); 
+1
source

Well, you can use UNION ALL

Sort of

 SELECT info FROM orders WHERE id IN (1, 2) UNION ALL SELECT info FROM orders WHERE id IN (2) 
0
source

Source: https://habr.com/ru/post/1501343/


All Articles