mysql select multi rows equal from row 2

Question

I have two tables:

TableA          TableB
ID_A            ID_B
P1              Text
P2
P3
P4

-

TableB
ID_B    Text
1       A
2       B
3       C
4       D
5       E
6       F
7       G

-

TableA
ID_A    P1  P2  P3  P4
1       7   5   6   6

thanks to Nick who helped me USER Nick

SELECT a.*, b.*
FROM (SELECT ID_A, P1 AS P FROM TableA
  UNION ALL
  SELECT ID_A, P2 FROM TableA
  UNION ALL
  SELECT ID_A, P3 FROM TableA
  UNION ALL
  SELECT ID_A, P4 FROM TableA) p
JOIN TableA a ON a.ID_A = p.ID_A
JOIN TableB b ON b.ID_B = p.P
ORDER BY a.ID_A, b.ID_B

I get:

ID_A    P1  P2  P3  P4    ID_B Text
1       7   5   6   6      5    E
1       7   5   6   6      6    F
1       7   5   6   6      6    F
1       7   5   6   6      7    G

How can I get this?

ID_A    P1  P2  P3  P4    ID_B Text
1       7   5   6   6      7    G
1       7   5   6   6      5    E
1       7   5   6   6      6    F
1       7   5   6   6      6    F

1° row P1 = ID_B

2° row P2 = ID_B

3° row P3 = ID_B

4° row P4 = ID_B

Demo on db-fiddle

Rakesh Jakhar · Answer 1 · 2019-04-23T12:35:47.960

You can use CASE, WHEN for a custom sort order. This will show the result in the desired sorting

 SELECT a.*, b.*
 FROM (SELECT ID_A, P1 AS P FROM TableA
  UNION ALL
  SELECT ID_A, P2 FROM TableA
  UNION ALL
  SELECT ID_A, P3 FROM TableA
  UNION ALL
  SELECT ID_A, P4 FROM TableA) p
 JOIN TableA a ON a.ID_A = p.ID_A
 JOIN TableB b ON b.ID_B = p.P
 ORDER BY CASE b.ID_B
     WHEN 7 THEN 4
     WHEN 5 THEN 3
     ELSE 2
 END DESC

score 0 · Answer 2 · answered Apr 23 '19 at 12:46

0

SELECT * FROM TableA as a1
left join TableB as b1 on ID_B = p1
union ALL
SELECT * FROM TableA
left join TableB on ID_B = p2
union ALL
SELECT * FROM TableA
left join TableB on ID_B = P3
union ALL
SELECT * FROM TableA
left join TableB on ID_B = p4

answered Apr 23 '19 at 12:46

Mohasinali Nodoliya

55
8

score 0 · Accepted Answer · answered Apr 23 '19 at 13:06

This is happening because there is no order in the derived table of P values. To make it possible to order that, we can add another field which represents the P number (as in P1, P2 etc.) and order by that instead of ID_B:

SELECT a.*, b.*
FROM (SELECT 1 AS PNUM, ID_A, P1 AS P FROM TableA
      UNION ALL
      SELECT 2, ID_A, P2 FROM TableA
      UNION ALL
      SELECT 3, ID_A, P3 FROM TableA
      UNION ALL
      SELECT 4, ID_A, P4 FROM TableA) p
JOIN TableA a ON a.ID_A = p.ID_A
JOIN TableB b ON b.ID_B = p.P
ORDER BY a.ID_A, p.PNUM

Output:

ID_A    P1  P2  P3  P4  ID_B    Text
1       7   5   6   6   7       G
1       7   5   6   6   5       E
1       7   5   6   6   6       F
1       7   5   6   6   6       F

Demo on dbfiddle

thanks 1000 Nick You are a genius of the sql language. My knowledge of sql is basic. Thanks to everyone who contributed, but the best solution is Nick. — carmelocony, Apr 23 '19 at 15:25

mysql select multi rows equal from row 2

3 Answers3