CODEWITHSUNDEEP
phpmysqljoin
Shikoru
Shikoru

Reputation: 23

Mysql Join non matching tables with one to many relationship

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"

Upvotes: 0

Views: 111

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26363

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

Upvotes: 1

Related Questions