query multiple rows

[sandy1028]

How to write a query ,

 

 

TABLE "table1" has the FIELD's  cid, MID, VALUE

For 1 cid there are mulitple rows.

 

 

Example : cid 1711 there are mulitple rows.

select * from table1 where cid=1711

 

Result :

cid  MID VALUE

1711 2  name1

1711 5  1

1711 3  2

 

How TO QUERY the TABLE TO find OUT the COUNT cid's, where value for mid=2 is "%name1%' and value for mid=5 is between 0 to 100.

 

 

select count(*) from table1 where mid=2 and value like '%name1%' and (mid=5 and value >=0 and value <=100)

This results 0 rows.

 

This should return content_id 1711. how to modify the above query

 

 

[kickstart]

Hi

 

Not tested, but something like this should do it:-

 

SELECT COUNT(*)
FROM TABLE a
INNER JOIN Table b
ON a.cid = b.cid
WHERE a.mid = 2
AND a.value = '%name1%'
AND b.mid = 5
AND b.value BETWEEN 0 AND 100

 

All the best

 

Keith