List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:November 23 2012 7:13am
Subject:Re: Basic SELECT help
View as plain text  
Claudio

This is the solution i decided to go for as provided in a previous response.

Thanks
Neil

On 23 Nov 2012, at 00:41, Claudio Nanni <claudio.nanni@stripped> wrote:

> 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.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
>>>> 
>>>> 
> 
> 
> -- 
> Claudio
> 
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