This small SQL error is bugging me. It doesn't seem to be a problem with the query, just the scope(?), examples work best:
SELECT ocp.*, oc.*, GROUP_CONCAT( u.username SEPARATOR ', ') AS `memjoined`
FROM gangs_ocs_process ocp, gangs_ocs oc
LEFT JOIN users u ON u.userid IN ( ocp.membersin )
WHERE ocp.ocid =1 AND ocp.gangid =1 AND oc.oc_name = ocp.crimename
GROUP BY ocp.ocid
LIMIT 0 , 30
Theres a column (gangs_ocs_process.membersin) which has a list of IDs that have joined (ie 1,2,5). I'm trying to get the usernames for each of these IDs (from the users table) in one go.
The problem is LEFT JOIN users u ON u.userid IN ( ocp.membersin )
If I substitue 1,2,4 in for ocp.membersin (putting the literal list instead of column name), it works ok. It returns a column that has the usernames (image). However, if I leave in the ocp.membersin, I get this error:
#1054 - Unknown column 'ocp.membersin' in 'on clause'
This is the first time I've even used IN in left joins so I'm a bit lost.
Any help would be great :)
I don't think that "IN" will work for this syntax. MySQL expects IN to be something akin to a dataset, not a delimited string. I think you need to find a way to take membersin, expand it into a dataset MySQL can work with (maybe a temporary table), and join on that.
