MySQL IN() Clause with Sub Select

[Korizon67]

I have a Table with the follwing,

+----+------+-----------+
| id | type | vendor |
+----+------+-----------+
| 1 | ppc | google |
| 2 | ppc | quigo |
| 3 | ppc | quigo-aol |
| 4 | ppc | pulse360 |
| 5 | ppc | yahoo |
| 6 | ppc | msn |
+----+------+-----------+

if i run...

SELECT GROUP_CONCAT('\'',`lcr_vendors`.`vendor`,'\'') FROM `lcr_vendors` WHERE `lcr_vendors`.`type` = 'ppc'

I get,

'google','quigo','quigo-aol','pulse360','yahoo','test','msn'

I am running the following...

SELECT `lrc_visits_tmp`.*
FROM `lrc_visits_tmp`
WHERE `lrc_visits_tmp`.`vendor` IN(SELECT GROUP_CONCAT('\'',`lcr_vendors`.`vendor`,'\'') FROM `lcr_vendors` WHERE `lcr_vendors`.`type` = 'ppc');

it returns no results, if I run ...

SELECT `lrc_visits_tmp`.*
FROM `lrc_visits_tmp`
WHERE `lrc_visits_tmp`.`vendor` IN('google','quigo','quigo-aol','pulse360','yahoo','msn');

Anyone have any clue why this query is failing?

SELECT version(); returns 5.0.51b-community

Thanks

Mike

 

[Korizon67]

I meant to say

it returns no results, if I run ...

SELECT `lrc_visits_tmp`.*
FROM `lrc_visits_tmp`
WHERE `lrc_visits_tmp`.`vendor` IN('google','quigo','quigo-aol','pulse360','yahoo','msn');

I GET results

Sorry Mike

 

[r937]

instead of this --

GROUP_CONCAT('\'',`lcr_vendors`.`vendor`,'\'')

you should write this --

GROUP_CONCAT(CONCAT('''',lcr_vendors.vendor,''''))

and then try your IN again



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon