MySQL query statement question

[darian]

Hello all,

 

I hope one of you will be able to help with what may turn out to be an (embarrassingly) easy question.

 

I want to be able to query a database for all companies fulfulling, say, two criteria. Let\'s say country and nature of business. Each company can have multiple entries for each of these criteria. If I create a table so that all information about one company is on one row, using comma separation for multiple entries, it might look like:

 

company_id  country_id  nature_id  

1           3,4,        1,5,     

2           8,4,        2,5, 

 

Now, here I thought I could create a query to find companies operating in country 4, with nature 5 as:

select * from company where 4 in (country_id) and 5 in (nature_id)

but it doesn\'t work. Only if, for example, 4 is the first in the comma separated list of country_ids does the \'in\' statement match. Any ideas what the problem may be or a better query statement?

 

Now, I\'m sure many of you relational database experts would castigate me for using such comma separation here anyway, so instead, the information could be stored as:

 

company_id  criteria  value

1           country      3

1           country      4

1           nature       1

2           country      8

2           country      4

2           nature       2

2           nature       5

So the question now is, what query do I use here to select a company with country 4 and nature 5?

 

select co1.id from company as co1, company as co2

where co1.company_id=co2.company_id

and co1.criteria=\'country\' and co1.value=4

and co2.criteria=\'nature\' and co2.value=5

but this seems pretty horrendous. Plus, in fact I\'ll be using four or five criteria, not just two which makes it much worse.

 

Any suggestions?

 

Thanks very much in advance for your help.

 

Best,

Darian