Jump to content

MySQL query statement question


darian

Recommended Posts

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.