List:General Discussion« Previous MessageNext Message »
From:Ben Mildren Date:November 22 2012 3:25pm
Subject:Re: Basic SELECT help
View as plain text  
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
>>>
>>>
Thread
Fwd: Basic SELECT helpMichael Dykman22 Nov
  • Re: Basic SELECT helpNeil Tompkins22 Nov
    • Re: Basic SELECT helpMichael Dykman22 Nov
      • Re: Basic SELECT helpNeil Tompkins22 Nov
        • Re: Basic SELECT helpMichael Dykman22 Nov
    • Re: Basic SELECT helpBen Mildren22 Nov
      • Re: Basic SELECT helpBen Mildren22 Nov
        • Re: Basic SELECT helpBen Mildren22 Nov
      • Re: Basic SELECT helpClaudio Nanni23 Nov
        • Re: Basic SELECT helpNeil Tompkins23 Nov
Re: Basic SELECT helpMogens Melander23 Nov
  • Re: Basic SELECT helpdivesh kamra23 Nov