SQL problem, LEFT JOIN [..] IN()

Question

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 :)

Answer 1

The reason you can't get it to work is because first you need to get your database NORMALIZED. You should NEVER, EVER have a list of ID's in a single column.

Answer 2

This is a bad way to keep membership.

But if you still need to live with it, you may try REGEXP matching to test for membership:

SELECT ocp.*, oc.*, GROUP_CONCAT( u.username SEPARATOR ', ') AS `memjoined`
FROM gangs_ocs_process ocp
LEFT JOIN users u ON (ocp.membersin RLIKE CONCAT('(^|,)[[:blank:]]?', userid, '[[:blank:]]?($|,)'))
JOIN gangs_ocs oc ON (ocp.ocid = 1 AND ocp.gangid = 1 AND oc.oc_name = ocp.crimename)
GROUP BY ocp.ocid
LIMIT 0 , 30

Answer 3

After taking another look, I think your problem is trying to aggregate at the wrong point as well as the IN syntax and that you should aggregate in a subquery restricted by the contents of the IN. I don't know enough about your schema to make this out of the box correct, but you want something like this. SomeKeyfield should relate back to gangs_ocs_process

SELECT ocp.*, oc.*, u.Memjoined
FROM gangs_ocs_process ocp, gangs_ocs oc
LEFT JOIN (Select SomeKeyField, GROUP_CONCAT( u.username SEPARATOR ', ') as memjoined
      from  users where userid in
      (select membersin from gangs_ocs_process 
             where [whatever conditions] )
             Group By SomeKeyField) u on ocp.SomeKeyField = u.SomeKeyField

WHERE ocp.ocid =1 AND ocp.gangid =1 AND oc.oc_name = ocp.crimename
GROUP BY ocp.ocid
LIMIT 0 , 30

Answer 4

Are you sure 'membersin' is in the 'gangs_ocs_process' table, and not the 'gangs_ocs' table?

Answer 5

Have you tried cmsjr example by putting it in the WHERE clause?

You could also insert DISTINCT in that Select statement as well

e.g.

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 
WHERE ocp.ocid =1 
AND ocp.gangid =1 
AND oc.oc_name = ocp.crimenameand u.userid IN ( select DISTINCT membersin from gangs_ocs_process where [your conditions]  )
GROUP BY ocp.ocid
LIMIT 0 , 30

Answer 6

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.

Answer 7

If you have delimited strings in your table, you have a design problem in your database. Add a new table to hold these values.