SQL join two tables respecting order

Question

I have got two tables. Table1 stores the position order of the codes. Table2 stores information of each code. I'm trying to do a query which done a id, returns the order of each code and its associated information.

TABLE1
+-------+-----------+----------+-----------+-----------+-----------+
| id    | position1 | positon2 | position3 | position4 | position5 |
+-------+-----------+----------+-----------+-----------+-----------+
| id1   | AA        | CC       | BB        | EE        | DD        |
|-------|-----------|----------|-----------|-----------|-----------|
| id2   | BB        | AA       | DD        | EE        | CC        |
+-------+-----------+----------+-----------+-----------+-----------+

TABLE2
+------+------------+--------------------+
| code | name       | active_icon        | 
+------+------------+--------------------+
| AA   | nameA      | iconA              |
| BB   | nameB      | iconB              |
| CC   | nameC      | iconC              |
| DD   | nameD      | iconD              |
| EE   | nameE      | iconE              |
+------+------------+--------------------+

Done an id (for example id = id2), the result I'm expecting is:

+----------------+------+------------+--------------------+
| position       | code | name       | active_icon        |
+----------------+------+------------+--------------------+
|              1 | BB   | nameB      | iconB              |
|              2 | AA   | nameA      | iconA              |
|              3 | DD   | nameD      | iconD              |
|              4 | EE   | nameE      | iconE              |
|              5 | CC   | nameC      | iconC              |
+----------------+------+------------+--------------------+

Any ideas? I think there must be an INNER JOIN but I don't know how to structure it.

Design your table properly. Things that are supposed to have an easy solution will be complicated if your table structure is messed up. For example, this... — Eric, Apr 09 '18 at 17:52

score 0 · Answer 1 · answered Mar 02 '14 at 12:29

This is a denormalized design. It'd be MUCH easier if you'd model it properly. It'd be far more extensible, too.

I'd give table 1 a column for position and a foreign key to table 2 for the code. You could have as many codes as you wished then. Reordering would mean changing position values. The JOIN writes itself then: just add an ORDER BY position.

score 0 · Answer 2 · answered Mar 02 '14 at 12:33

0

SELECT position, t1.code, name, active_icon
FROM (SELECT 1 AS position, id, position1 AS code
      FROM Table1
      UNION
      SELECT 2 AS position, id, position2 AS code
      FROM Table1
      UNION
      SELECT 3 AS position, id, position3 AS code
      FROM Table1
      UNION
      SELECT 4 AS position, id, position4 AS code
      FROM Table1
      UNION
      SELECT 5 AS position, id, position5 AS code
      FROM Table1) AS t1
JOIN Table2 AS t2
ON t1.code = t2.code
WHERE t1.id = :ID
ORDER BY position

answered Mar 02 '14 at 12:33

Barmar

741,623
53
500
612

While I'm sure it works, I hope we're not seriously advocating this as a solution. :-( – Strawberry Mar 02 '14 at 13:23
I wrote a comment recommending that he normalize his database. But that's not an answer, so I didn't put it here. – Barmar Mar 03 '14 at 05:46

score 0 · Answer 3 · edited May 23 '17 at 10:31

0

You can try use a PIVOT operation. But in MySQL pivot is unimpemented, but you can emulate it manually. See MySQL pivot table for examples.

edited May 23 '17 at 10:31

Community

1
1

answered Mar 02 '14 at 12:34

zealot

237
1
4

SQL join two tables respecting order

3 Answers3