From: Neil Tompkins
Date: November 23 2012 7:13am
Subject: Re: Basic SELECT help
List-Archive: http://lists.mysql.com/mysql/228711
Message-Id: <E45AE534-7216-4235-9684-47D56793EBC6@googlemail.com>
MIME-Version: 1.0 (1.0)
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable

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 ;)
>=20
> SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=3D=
<num of params>
>=20
> The only bad is the hardcoded parameter in the HAVING, may be it might be i=
mproved.
>=20
> 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 co=
de being so hard.
>=20
> Cheers
>=20
> Claudio
>=20
>=20
>>=20
>> On 22 November 2012 15:01, Neil Tompkins <neil.tompkins@stripped> w=
rote:
>>> Michael,
>>>=20
>>> Thanks this kind of works if I'm checking two types.  But what about if I=

>>> have 5 types ?
>>>=20
>>> On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman <mdykman@stripped> wrot=
e:
>>>=20
>>>> response did not go to the list..
>>>>=20
>>>>=20
>>>> I assume that you mean the id must be associated with both type=3D5 AND=

>>>> type=3D2 as opposed to type=3D5 OR type=3D2;
>>>>=20
>>>> in some dialect of SQL (not mysql) you can do this:
>>>> select distinct id from 'table' where type=3D5
>>>> intersect
>>>> select distinct id from 'table' where type=3D2
>>>>=20
>>>>=20
>>>> As INTERSECT is not avilable under mysql, we will have to go the JOIN r=
oute
>>>>=20
>>>> select distinct a.id from mytable a
>>>> inner join mytable b on (a.id=3Db.id)
>>>> where a.type=3D 2 and b.type =3D 5;
>>>>=20
>>>>  - michael dykman
>>>>=20
>>>> On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
>>>> <neil.tompkins@stripped> wrote:
>>>>> Hi,
>>>>>=20
>>>>> I'm struggling with what I think is a basic select but can't think how=
 to
>>>>> do it : My data is
>>>>>=20
>>>>> id,type
>>>>>=20
>>>>> 1000,5
>>>>> 1001,5
>>>>> 1002,2
>>>>> 1001,2
>>>>> 1003,2
>>>>> 1005,2
>>>>> 1006,1
>>>>>=20
>>>>> =46rom this I what to get a distinct list of id where the type equals 2=

>>>> and 5
>>>>> Any ideas ?
>>>>>=20
>>>>> Neil
>>>>=20
>>>>=20
>>>> --
>>>>  - michael dykman
>>>>  - mdykman@stripped
>>>>=20
>>>>  May the Source be with you.
>>>>=20
>>>>=20
>>>> --
>>>>  - michael dykman
>>>>  - mdykman@stripped
>>>>=20
>>>>  May the Source be with you.
>>>>=20
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:    http://lists.mysql.com/mysql
>>>>=20
>>>>=20
>=20
>=20
> --=20
> Claudio
>=20