List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:November 23 2012 12:41am
Subject:Re: Basic SELECT help
View as plain text  
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