one sql statment

[jonniejoejonson]

+----+-------+----------+--------+-----------+

| id | netId | optionId | userId | value    |

+----+-------+----------+--------+-----------+

|  1 |  1    |    1    |      1 | dog      |

|  2 |  1    |    2    |      1 | cat      |

|  3 |  1    |    3    |      5 | dog      |

|  4 |  1    |    4    |      1 | dog      |

|  5 |  1    |    4    |      9 | rabbit    |

|  6 |  1    |    5    |      1 | fox      |

|  7 |  2    |    6    |      2 | dog      |

+----+-------+----------+--------+-----------+

 

 

Hello this may seem like a complicated and stupid question,

but using some ot the data above as an example...

 

i want to select unique userId's of the rows where

 

netId=1

AND

(optionId=1 AND value='dog')

AND

(optionId=2 AND value='cat')

 

 

 

 

can i do this in one select statement?...

or must i do it in two select statements and then

discard the results that dont fit my statement.

regards J.

[Festy]

Is this what you want ?

 

SELECT DISTINCT(userId) 
FROM table_name 
WHERE netId=1 
AND (optionId=1 AND value='dog') 
AND  (optionId=2 AND value='cat')

[jonniejoejonson]

this is indeed what i mean, however how do you this to work?

thanks for your support.

[Festy]

this is indeed what i mean, however how do you this to work?

thanks for your support.

 

It's a working query. Just use it in your php code or run it in your mysql interface.

[jonniejoejonson]

Hello Festy, thanks for your continued support, however the statment deosnt pull the rows that are expected... if you imagine a database with just 2 rows and the following data

 

| id | netId | optionId | userId | value    |

+---+------+--------+--------+---------+

|  1 |  1    |    1      |      1  |  dog      |

|  2 |  1    |    2      |      1  |  cat      |

 

your sql statment, should pull out the one distinct result:  userId=1

however it is not pulling out any results.

if you change it for an OR instead of AND... then it pulls out userId=1,

however I dont want an OR i need and AND...

anyway i think you know what i mean... thanks again for your help.

I think i need to use a GROUPBY userId but i cant get my brain round it cos im

fairly new to this...

reagrds J