On 11/22/2012 04:10 PM, Ben Mildren wrote:
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
Ben you were almost there ;)

SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=<num of 
params>

The only bad is the hardcoded parameter in the HAVING, may be it might be 
improved.

Anyway if the query is handwritten then you just hand-modify that too,
if it is built from code I can't imagine counting the parameters in the code 
being so hard.

Cheers

Claudio



On 22 November 2012 15:01, Neil Tompkins <neil.tompk...@googlemail.com> wrote:
Michael,

Thanks this kind of works if I'm checking two types.  But what about if I
have 5 types ?

On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman <mdyk...@gmail.com> wrote:

response did not go to the list..


I assume that you mean the id must be associated with both type=5 AND
type=2 as opposed to type=5 OR type=2;

in some dialect of SQL (not mysql) you can do this:
select distinct id from 'table' where type=5
intersect
select distinct id from 'table' where type=2


As INTERSECT is not avilable under mysql, we will have to go the JOIN route

select distinct a.id from mytable a
inner join mytable b on (a.id=b.id)
where a.type= 2 and b.type = 5;

  - michael dykman

On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
<neil.tompk...@googlemail.com> wrote:
Hi,

I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

 From this I what to get a distinct list of id where the type equals 2
and 5
Any ideas ?

Neil


--
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.


--
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql




--
Claudio

Reply via email to