List:General Discussion« Previous MessageNext Message »
From:Mogens Melander Date:November 23 2012 5:47am
Subject:Re: Basic SELECT help
View as plain text  
Ok, to make up for my bad joke, here's the answer
to the original question.

DROP TABLE IF EXISTS `test`.`atest`;
CREATE TABLE  `test`.`atest` (
  `id` int(10) unsigned NOT NULL,
  `type` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into atest(id,type)
values(1000,5)
,(1001,5)
,(1002,2)
,(1001,2)
,(1003,2)
,(1005,2)
,(1006,1);

SELECT DISTINCT id
FROM atest
WHERE `type` = 2 OR `type` = 5
GROUP BY id
HAVING count(DISTINCT `type`) = 2;

On Thu, November 22, 2012 22:16, Michael Dykman wrote:
> Mogens,
>
> Platform could not be less relevant to a question of MySql syntax.
> The techniques we have been discussing have been available to every
> version of MySql post v3.23 and the class/job function he is applying
> it to is neither relevant to the problem nor any of our business,
> unless he volunteers to share it. Excepting only the working
> assumption that he is using a MySql version released in this century,
> I don't know how this would have informed my analysis or response.
>
>  - michael dykman
>
> On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander <mogens@stripped>
> wrote:
>>
>> On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
>>> Basically I only what to return the IDs that have both types.
>>>
>>
>> And that's exactly what below statement will return.
>>
>> You forgot to include what platform you are on,
>> which version of MySQL you are running and
>> what class you are attending.
>>
>> All necessary information to provide a sufficient help.
>>
>>>
>>> On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
>>> <marek.gutowski@stripped>wrote:
>>>
>>>> SELECT DISTINCT id FROM table WHERE type IN ('2','5')
>>>>
>>>> should work
>>>>
>>>>
>>>> On 22 November 2012 14:30, 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
>>>>>
>>>>
>>>>
>>>
>>> --
>>> This message has been scanned for viruses and
>>> dangerous content by MailScanner, and is
>>> believed to be clean.
>>>
>>>
>>
>>
>> --
>> Mogens Melander
>> +66 8701 33224
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is
>> believed to be clean.
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql
>>
>
>
>
> --
>  - michael dykman
>  - mdykman@stripped
>
>  May the Source be with you.
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>


-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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