From: Ben Mildren
Date: November 22 2012 3:25pm
Subject: Re: Basic SELECT help
List-Archive: http://lists.mysql.com/mysql/228692
Message-Id: <CAGVRu7J5AeMj=0nZBwv5E=SXw-xoX94bYePeWfJiCfxB1xi+Hw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1

Ah read it quickly and misread your requirement.  Joins are likely FTW
here.  The alternative would be to do something like this, but I'd opt
for the joins if you have a reasonably sized data set:

SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
WHERE id IN(x,y,z) GROUP BY id HAVING listid = 'x,y,z';

On 22 November 2012 15:10, Ben Mildren <ben.mildren@stripped> wrote:
> SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
>
> On 22 November 2012 15:01, Neil Tompkins <neil.tompkins@stripped> 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 <mdykman@stripped> 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.tompkins@stripped> 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
>>>  - mdykman@stripped
>>>
>>>  May the Source be with you.
>>>
>>>
>>> --
>>>  - michael dykman
>>>  - mdykman@stripped
>>>
>>>  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
>>>
>>>