Re: Basic SELECT help
Hi Neil,
On 11/22/2012 7:14 PM, h...@tbbs.net wrote:
2012/11/22 14:30 +, Neil Tompkins
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 ?
This ugly one, which generalizes:
select id,group_concat(type) AS tl from the_table group by id having
find_in_set('2',tl) and find_in_set('5',tl)
Ugly becaus it involves so much converting between number string.
For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my tl and a string, say '1,2', to a procedure that using
SUBSTRING_INDEX taking the strings for arrays ensures that all found in the first string
is also in the second string. There are times when I wish SQL had arrays.
The fun part of solving this is to remember that SQL is a set-oriented
language. For each element in the set, none of them can be both 2 and 5
at the same time. So, you have to build two sets and check to see which
rows are in both.
One pattern works if you need to aggregate for just a few terms
SELECT a.id
from (select distinct id from mytable where type=2) a
INNER JOIN (select distinct id from mytable where type=5) b
on a.id=b.id
However, this gets numerically very expensive with more than a few JOINS
to the pattern. Also, there is no index on either of the temporary
results (a or b) so this is a full Cartesian product of both tables.
That means that although it gives you a correct answer, it will not
scale to 10's of rows (or more) in either set.
So, here is a way to assemble the same result that uses much less
resources. Remember, each row you want is a member of a set.
CREATE TEMPORARY TABLE tmpList (
id int
, type int
, PRIMARY KEY (id,type)
)
INSERT IGNORE tmpList
SELECT id,type
FROM mytable
WHERE type in (2,5)
SELECT id, count(type) hits
FROM tmplist
GROUP BY id
HAVING hits=2
DROP TEMPORARY TABLE tmpList
Can you see why this works?
I created an indexed subset of rows that match either value (2 or 5) but
only keep one example of each. I accomplished that by the combination of
PRIMARY KEY and INSERT IGNORE. Then I counted many type values each ID
value represented in the subset. If I looked for 2 terms and I ended up
with hits=2, then I know that those ID values matched on both terms.
You can expand on this pattern to also do partial (M of N search terms)
or best-fit determinations.
I hope this was the kind of help you were looking for.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
Hi
Is there is performance issue from this query on more then 5-10 million data
On Fri, Nov 23, 2012 at 11:17 AM, Mogens Melander mog...@fumlersoft.dkwrote:
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 mog...@fumlersoft.dk
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.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
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
- mdyk...@gmail.com
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.comwrote:
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
Re: Basic SELECT help
Hi Neil Would something like this work. SELECT DISTINCT id,type from your_table WHERE type=2 OR type=5; Mike - Original Message - From: Neil Tompkins neil.tompk...@googlemail.com To: [MySQL] mysql@lists.mysql.com Sent: Thursday, November 22, 2012 9:30 AM Subject: Basic SELECT help 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.comwrote:
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
Re: Basic SELECT help
U can remove the type field it will work
On Nov 22, 2012 8:21 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.com
wrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.com
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
Re: Basic SELECT help
How about if I have the following
SELECT DISTINCT id
FROM my_table
WHERE (type = 3 OR type = 28 OR type = 1)
In this instance, for the id 280149 it only has types 3 and 28 but *not *1.
But using the OR statement returns id 280149
On Thu, Nov 22, 2012 at 2:53 PM, Benaya Paul benayap...@gmail.com wrote:
U can remove the type field it will work
On Nov 22, 2012 8:21 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.com
wrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.com
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
Re: Basic SELECT help
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 mdyk...@gmail.com 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.tompk...@googlemail.com 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com 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
Re: Basic SELECT help
Keep joining I think. In the absence of intersect (which incurs the cost of a query per type anyhow ), this join pattern is the only option I can think of. On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com 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 mdyk...@gmail.com wrote: response did not go to the list.. I assume that you mean the id must be associated with bo... -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe...
Re: Basic SELECT help
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com 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 mdyk...@gmail.com 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.tompk...@googlemail.com 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
Do you know if I had multiple joins there would be a performance issue ? On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman mdyk...@gmail.com wrote: Keep joining I think. In the absence of intersect (which incurs the cost of a query per type anyhow ), this join pattern is the only option I can think of. On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com 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 mdyk...@gmail.com wrote: response did not go to the list.. I assume that you mean the id must be associated with bo... -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe...
Re: Basic SELECT help
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.mild...@gmail.com wrote: SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com 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 mdyk...@gmail.com 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.tompk...@googlemail.com 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
Of course there is a cost for the join, each link being a distinct lookup query but that is the same cost the INTERSECT would impose. It is not a bad as multiple joins generally might be as all the lookups are against the same key in the same table which should keep that index in ram. (type is indexed, yes?) As you no doubt have noticed, the problem with these solutions: SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; is that they returns ids that have ANY of those values which is not what you are asking for, If your dataset is HUGE, there might be a performance problem which might force you to reformulate as: create temporary table `mytemp` select id, type from `mytable` WHERE type IN(x,y,z); select distinct a.id from `mytemp` a inner join `mytemp` b on (a.id=b.id) where a.type= 2 and b.type = 5; -- repeat inner join as needed drop table mytemp; On Thu, Nov 22, 2012 at 10:09 AM, Neil Tompkins neil.tompk...@googlemail.com wrote: Do you know if I had multiple joins there would be a performance issue ? On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman mdyk...@gmail.com wrote: Keep joining I think. In the absence of intersect (which incurs the cost of a query per type anyhow ), this join pattern is the only option I can think of. On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com 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 mdyk...@gmail.com wrote: response did not go to the list.. I assume that you mean the id must be associated with bo... -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe... -- - michael dykman - mdyk...@gmail.com 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
Re: Basic SELECT help
*HAVING typelist = 'x,y,z'; On 22 November 2012 15:25, Ben Mildren ben.mild...@gmail.com 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.mild...@gmail.com wrote: SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com 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 mdyk...@gmail.com 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.tompk...@googlemail.com 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Basic SELECT help
Having watched responses go back and forth, I'll throw my cave-man approach into the mix. select id from (select distinct id, count(*) from my_table where type in (2,5) group by id having count(*) = 2)a; And addressing one of your concerns about more than two variables...in this example,you would have to update the values in the where clause and the count. It ain't the prettiest...and not ideal from a performance perspective, but it does work. I guess it kind of depends on how far the real-world problem strays from this small example. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Thursday, November 22, 2012 8:30 AM To: [MySQL] Subject: Basic SELECT help 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
Assuming that (id,type) is unique in the source data, that is a pretty elegant method: select id from (select distinct id, count(*) from my_table where type in (2,5) group by id having count(*) = 2)a; -- - michael dykman - mdyk...@gmail.com 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
Re: Basic SELECT help
By unique you mean that no id and type would be duplicated like 1,1 1,1 Yes it isn't possible for duplicate id and type in more than 1 row On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote: Assuming that (id,type) is unique in the source data, that is a pretty elegant method: select id from (select distinct id, count(*) from my_table where type in (2,5) group by id having count(*) = 2)a; -- - michael dykman - mdyk...@gmail.com 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
Re: Basic SELECT help
When trying this query I get FUNCTION id does not exist On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote: select id from (select distinct id, count(*) from my_table where type in (2,5) group by id having count(*) = 2)a;
Re: Basic SELECT help
Ignore that it does work fine. Sorry On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote: Assuming that (id,type) is unique in the source data, that is a pretty elegant method: select id from (select distinct id, count(*) from my_table where type in (2,5) group by id having count(*) = 2)a; -- - michael dykman - mdyk...@gmail.com 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
Re: Basic SELECT help
Doing a EXPLAIN on the SELECT statement it is using Using where; Using temporary; Using filesort with 14000 rows of data. How best to improve this; when I already have indexed on id and type On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote: Assuming that (id,type) is unique in the source data, that is a pretty elegant method: select id from (select distinct id, count(*) from my_table where type in (2,5) group by id having count(*) = 2)a; -- - michael dykman - mdyk...@gmail.com 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
Re: Basic SELECT help
On Thu, Nov 22, 2012 at 11:58 AM, Neil Tompkins neil.tompk...@googlemail.com wrote: By unique you mean that no id and type would be duplicated like 1,1 1,1 Yes it isn't possible for duplicate id and type in more than 1 row Yes, that's exactly what I meant. - mdyk...@gmail.com 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
Re: Basic SELECT help
Hmmm. OR, IN and HAVING pops up. On Thu, November 22, 2012 15:30, Neil Tompkins 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
Re: Basic SELECT help
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.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
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
Re: Basic SELECT help
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 mog...@fumlersoft.dk 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.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
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
- mdyk...@gmail.com
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
Re: Basic SELECT help
2012/11/22 14:30 +, Neil Tompkins
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 ?
This ugly one, which generalizes:
select id,group_concat(type) AS tl from the_table group by id having
find_in_set('2',tl) and find_in_set('5',tl)
Ugly becaus it involves so much converting between number string.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
2012/11/22 14:30 +, Neil Tompkins
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 ?
This ugly one, which generalizes:
select id,group_concat(type) AS tl from the_table group by id having
find_in_set('2',tl) and find_in_set('5',tl)
Ugly becaus it involves so much converting between number string.
For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my tl and a string, say '1,2', to a procedure that using
SUBSTRING_INDEX taking the strings for arrays ensures that all found in the
first string is also in the second string. There are times when I wish SQL had
arrays.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
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.tompk...@googlemail.com 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 mdyk...@gmail.com 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.tompk...@googlemail.com 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com 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
Re: Basic SELECT help
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 mog...@fumlersoft.dk
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.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
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
- mdyk...@gmail.com
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
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.na...@gmail.com 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.tompk...@googlemail.com 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 mdyk...@gmail.com 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.tompk...@googlemail.com 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
