List:General Discussion« Previous MessageNext Message »
From:Ben Mildren Date:November 22 2012 3:27pm
Subject:Re: Basic SELECT help
View as plain text  
*HAVING typelist = 'x,y,z';

On 22 November 2012 15:25, Ben Mildren <ben.mildren@stripped> wrote:
> 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