0

I have the following query (and it works fine):

   SELECT cd.id AS card_id,
          ct.id AS category_id,
          COUNT(cc.user_id) AS cnt
     FROM uiCards AS cd
     JOIN uiCardCategories AS ct USING (project_id)
LEFT JOIN uiCategories2Cards AS cc ON (cc.card_id = cd.id AND cc.stack_id = ct.id)
    WHERE cd.project_id = $projID
 GROUP BY cd.id, ct.id
 ORDER BY cd.id, ct.id

I also have a sting of numbers:

$exclude = '100,122,345';

I need to modify the string too exclude results found in the string. So I added:

AND cc.user_id NOT IN ($exclude)

below WHERE

WHERE cd.project_id = $projID
  AND cc.user_id NOT IN ($exclude)

It did not seem to work, so I tried to modify more, and the whole query collapsed on me.

UPDATE:

I got it! I added quotes: 

AND (FIND_IN_SET(cc.user_id, '$exclude') = 0 OR FIND_IN_SET(cc.user_id, '$exclude') IS NULL)
Improve this question
5
  • 5
    What's the error? I assume you're using PHP, echo mysql_error(); after the query. Commented Jun 27, 2011 at 20:18
  • 2
    also try printing out the complete sql statement before you call mysql_query() to see what was actually generated. Commented Jun 27, 2011 at 20:20
  • 2
    Because cc is in a LEFT JOIN, all the WHERE condition achieves is that NULL values for cc.user_id appear in the output when the condition is not met, presumably resulting in a count of 1 (I haven't actually tried this though). What are you actually trying to achieve? Commented Jun 27, 2011 at 20:20
  • @Neil, are you saying that it breaks because I have an empty $exclude string? There are values in it. Commented Jun 27, 2011 at 20:22
  • Here's the error I finally was able to get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2,3) = 0 OR FIND_IN_SET(cc.user_id, 1,2,3) IS NULL) GROUP BY ' at line 11 Commented Jun 27, 2011 at 21:01

2 Answers 2

Reset to default
2

The SQL IN clause doesn't allow a single variable to represent a list of values. The query, as-is, can only be run as dynamic SQL -- on any database. Even run dynamically, SQL will only interpret this example as a single string.

Secondly, because of using an OUTER JOIN (LEFT in this example), placement of criteria can drastically affect the results returned. Specifying criteria in the JOIN's ON clause will apply the criteria before the JOIN is made; using the WHERE clause means the criteria is applied after the JOIN, which could mean additional records you did not want included.

You could use the FIND_IN_SET function instead:

WHERE cd.project_id = $projID
  AND (FIND_IN_SET(cc.user_id, $excluded) = 0 OR 
       FIND_IN_SET(cc.user_id, $excluded) IS NULL)

..vs in the LEFT JOIN criteria:

LEFT JOIN uiCategories2Cards AS cc ON cc.card_id = cd.id 
                                  AND cc.stack_id = ct.id
                                  AND FIND_IN_SET(cc.user_id, $excluded) = 0
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

I tried that and my query returns no results. Earlier I thought it was due to the fact that I don't even SELECT cc.user_id, just count it... I'm lost.
@santa: Which version - the LEFT JOIN or the WHERE? Are you sure you're getting records you want to exclude without this filtration?
I get results OK without adding FIND_IN_SET(cc.user_id, $exclude) = 0 but I need to filter out the ones associated with the numbers in exclude string.
@santa: I made an update to handle when cc.userid is NULL -- have you tested with that?
Yes, thanks. I tested both and my query returns no results with that AND modification...
|
1

IN() requires a row set, but you are providing a string, so this won't work. Use the function FIND_IN_SET() instead.

Improve this answer

5 Comments

It is not a string. The user didn't use any ' or " around $excluded.
@Parkyprg: See: $exclude = '100,122,345';
@OMG Ponies: Now in the update it is FIND_IN_SET(cc.user_id, '$exclude') but in initial question: AND cc.user_id NOT IN ($exclude)
@Parkyprg, $exclude is a variable, but it's value is a string. You cannot pass a string value to IN(). Am I now being clear?
No, not at all. When you say $query = "SELECT * FROM MyTable WHERE field NOT IN ($arrayIDs)"; and $arrayIDs = '10,20,30'; then the query sent to MySQL will be: SELECT * FROM MyTable WHERE field NOT IN (10,20,30) - which is not a string. Hope it clarifies my point now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.