MySQL Where IN with an Array

Question

I've searched a bit but haven't found exactly what I'm looking for, so far. Basically I have a MySQL database with a couple tables (keywords, company and link tables). I want to be able to supply an array of keywords and find the associated companies. When I run the second query without the WHERE IN clause, it works. When I supply an array, it doesn't.

select  keywords.idkeywords into @keyId
from keywords where
keywords.keyword IN ('Entertainment', 'Public');

select distinct company.company_name
from keywords, keyword_to_company, company 
where keyword_to_company.keywordId = @keyId
  and keyword_to_company.compId = company.idcompany;

Gordon Linoff · Accepted Answer · 2014-03-29 22:27:38Z

2

Your query just doesn't make sense. First, you are trying to put multiple values in @keyid, but you can't do that. And, MySQL doesn't have the concept of table variables. You could use a temporary table.

Then the second query is worse. Does this query work for you?

select distinct c.company_name
from keywords k natural join
     keyword_to_company k2c natural join
     company c
where k.keyword IN ('Entertainment', 'Public') and
      k2c.compId = company.idcompany;

I'm only using natural join because you don't specify the join keys. In general, you should always specify the columns being joined, using either on or using.

answered Mar 29, 2014 at 22:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2233299 · Accepted Answer · 2014-03-31 18:38:13Z

0

Thanks... Your query didn't work..

This query does work however. Althought @keyId returns multiple rows, the query succeeds and results in a listing of associated companies. i agree that it shouldn't work, but it does.

select keywords.idkeywords into @keyId from keywords where keywords.keyword = 'Entertainment' and keywords.keyword = 'Public';

select distinct company.company_name from keywords, keyword_to_company, company where

keyword_to_company.keywordId = @keyId

and keyword_to_company.compId = company.idcompany;

answered Mar 31, 2014 at 18:38

user2233299

112 bronze badges

Collectives™ on Stack Overflow

MySQL Where IN with an Array

2 Answers 2

Comments

This query does work however. Althought @keyId returns multiple rows, the query succeeds and results in a listing of associated companies. i agree that it shouldn't work, but it does.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

This query does work however. Althought @keyId returns multiple rows, the query succeeds and results in a listing of associated companies. i agree that it shouldn't work, but it does.

Comments

Your Answer

Sign up or log in

Post as a guest

Related