tags:

views:

262

answers:

7

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

A: 

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.

Otávio Décio
I'm using (trying) IN because the membersin col has multiple values, and I'm getting multiple rows back from the users table (grouping them with GROUP_CONCAT()). I tried your method as well, but it still says unknown column.
damnitshot
Ok, then your problem is simple - your database is DENORMALIZED and that is the source of your problems.
Otávio Décio
A: 

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
Quassnoi
It seems to partly work, I moved it back to two joins like you posted. The error doesn't come up, however, even though the `membersin` column is '1,2,3', it only returns one `users` row (I used the same IN() syntax as above)
damnitshot
See updated post.
Quassnoi
A: 

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
cmsjr
A: 

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

Bart S.
Made me double check heh, but yes it is.
damnitshot
A: 

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
kevchadders
+2  A: 

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.

Jeremy DeGroot
Yes - I'm pretty sure that's it now. The column has 1,2,3, but turns it into `userid IN ('1,2,3')`, which only fetches one. I'll have to expand/use two queries
damnitshot
+2  A: 

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

Joel Coehoorn