0

I have the following tables

t1

id stage
1 1,2,3
2 2,3,4

t2

id t_id stage_id
1 1 2
2 1 1
3 1 3
4 2 2
5 2 4
6 2 3

I hope the result can first order by t2.t_id and then order by the value of t1.stage

like the following result

t_id stage_id
1 1
1 2
1 3
2 2
2 3
2 4

I have the following sql ,but it do not work.So what should I do?

SELECT
    t2.t_id,
    t2.stage_id
FROM
    t2
LEFT JOIN t1 ON t1.id = t2.t_id
GROUP BY
    t2.t_id,
    t2.stage_id
ORDER BY
    t2.t_id,
    field(t2.stage_id, t1.stage)
Mr.lu
  • 11
  • 4

1 Answers1

0

FIELD() requires each value to sort by to be a separate argument.

FIND_IN_SET() returns the index in a comma-separated string.

SELECT
    t2.t_id,
    t2.stage_id
FROM
    t2
LEFT JOIN t1 ON t1.id = t2.t_id
GROUP BY
    t2.t_id,
    t2.stage_id
ORDER BY
    t2.t_id,
    find_in_set(t2.stage_id, t1.stage)

Note that this only corrects the ordering criteria in your SQL. This won't necessarily return the first row in each group by that ordering (and will get an error if the ONLY_FULL_GROUP_BY SQL mode is enabled). See SQL Selecting from two Tables with inner join and limit for the correct way to do that.

Barmar
  • 741,623
  • 53
  • 500
  • 612