Mysql使用一对多关系加入非匹配表

Question

I am trying to get all results from table 1 (Reports) and join the other two tables onto it (users) and (workorders)

Reports has keys relating to Users and Workorders but they are stored in csv values. I am trying to peform something similar to this psuedo code

`SELECT * 
FROM reports 
LEFT JOIN users ON reports = (WHERE users.userID IN (reports.users))
LEFT JOIN workorders ON reports = (WHERE workorder.status IN (reports.filters) 
AND reports.reportid = 10
`

reports.users and reports.filters look like "1,2,3,4,5,6"

Answer 1

If I understand correctly (meaning that reports.users and reports.filters are strings of comma-delimited values), you need the FIND_IN_SET function for this:

SELECT * from reports 
LEFT JOIN users ON FIND_IN_SET(users.userID, reports.users)
LEFT JOIN workorders ON FIND_IN_SET(workorder.status, reports.filters) 
  AND reports.reportid = 10