MySQL select matching

I may be going at this completely wrong but at the moment I'm
stuck. I have a DB from a client and need to do several searches on
it. This one sentence is important because it's their DB, not mine. So
I can't modify the way the DB was created in the first place, I can only
work with what I have. And, whatever the solution to this might be, it
does NOT have to be strictly MySQL, it can also be a PHP solution (which
is why I'm sending it there as well.) So, having said that, consider
the following table:

+-------+-----+-----+---+
| 00001 | 123 | 0.0 | C |
| 00001 | 234 | 0.1 | D |
| 00001 | 345 | 0.0 | D |
| 00001 | 456 | 0.1 | C |
| 00001 | 567 | 0.1 | G |
| 00002 | 123 | 0.0 | C |
| 00002 | 234 | 0.1 | D |
| 00002 | 345 | 0.0 | D |
| 00003 | 234 | 0.1 | D |
| 00003 | 345 | 0.0 | D |
| 00003 | 123 | 0.0 | C |
| 00003 | 456 | 0.1 | C |
| 00003 | 567 | 0.1 | G |
| 00004 | 123 | 0.0 | C |
| 00004 | 234 | 0.1 | D |
| 00004 | 345 | 0.0 | D |
+-------+-----+-----+---+

mysql> select * from table where id='00001';
+-------+-----+-----+---+
| 00001 | 123 | 0.0 | C |
| 00001 | 234 | 0.1 | D |
| 00001 | 345 | 0.0 | D |
| 00001 | 456 | 0.1 | C |
| 00001 | 567 | 0.1 | G |
+-------+-----+-----+---+

Now, I have to find other IDs that match the above result. In the
table, that would be ID '00003' (and in the entire DB, there may be
others as well - I need to find all those IDs.) But, notice how ID 0003
isn't in the same order as ID 00001, but the data is still the same.

So how do I efficiently search through the DB to find other IDs
that matches the one I need? I can't imagine doing a for loop selecting
each ID and comparing their result to the one I'm starting with. If the
DB contains thousands upon thousands of rows, that might take a very
long time.

Open to suggestions.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL select matching

Not quite sure what the question is.

from:
> mysql> select * from table where id=3D'00001';
> +-------+-----+-----+---+
> | 00001 | 123 | 0.0 | C |
> | 00001 | 234 | 0.1 | D |
> | 00001 | 345 | 0.0 | D |
> | 00001 | 456 | 0.1 | C |
> | 00001 | 567 | 0.1 | G |
> +-------+-----+-----+---+

How do we deduce that you would want ID '00003' ?

This conversation would be easier if we gave names to those columns..

- michael dykman


On Mon, Jul 19, 2010 at 12:36 PM, Ashley M. Kirchner wr=
ote:
>
> =A0 =A0I may be going at this completely wrong but at the moment I'm stuc=
k. =A0I
> have a DB from a client and need to do several searches on it. =A0This on=
e
> sentence is important because it's their DB, not mine. =A0So I can't modi=
fy
> the way the DB was created in the first place, I can only work with what =
I
> have. =A0And, whatever the solution to this might be, it does NOT have to=
be
> strictly MySQL, it can also be a PHP solution (which is why I'm sending i=
t
> there as well.) =A0So, having said that, consider the following table:
>
> +-------+-----+-----+---+
> | 00001 | 123 | 0.0 | C |
> | 00001 | 234 | 0.1 | D |
> | 00001 | 345 | 0.0 | D |
> | 00001 | 456 | 0.1 | C |
> | 00001 | 567 | 0.1 | G |
> | 00002 | 123 | 0.0 | C |
> | 00002 | 234 | 0.1 | D |
> | 00002 | 345 | 0.0 | D |
> | 00003 | 234 | 0.1 | D |
> | 00003 | 345 | 0.0 | D |
> | 00003 | 123 | 0.0 | C |
> | 00003 | 456 | 0.1 | C |
> | 00003 | 567 | 0.1 | G |
> | 00004 | 123 | 0.0 | C |
> | 00004 | 234 | 0.1 | D |
> | 00004 | 345 | 0.0 | D |
> +-------+-----+-----+---+
>
> mysql> select * from table where id=3D'00001';
> +-------+-----+-----+---+
> | 00001 | 123 | 0.0 | C |
> | 00001 | 234 | 0.1 | D |
> | 00001 | 345 | 0.0 | D |
> | 00001 | 456 | 0.1 | C |
> | 00001 | 567 | 0.1 | G |
> +-------+-----+-----+---+
>
> =A0 =A0Now, I have to find other IDs that match the above result. =A0In t=
he table,
> that would be ID '00003' (and in the entire DB, there may be others as we=
ll
> - I need to find all those IDs.) =A0But, notice how ID 0003 isn't in the =
same
> order as ID 00001, but the data is still the same.
>
> =A0 =A0So how do I efficiently search through the DB to find other IDs th=
at
> matches the one I need? =A0I can't imagine doing a for loop selecting eac=
h ID
> and comparing their result to the one I'm starting with. =A0If the DB con=
tains
> thousands upon thousands of rows, that might take a very long time.
>
> =A0 =A0Open to suggestions.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL select matching

On 7/19/2010 12:36 PM, Ashley M. Kirchner wrote:
>
> I may be going at this completely wrong but at the moment I'm
> stuck. I have a DB from a client and need to do several searches on
> it. This one sentence is important because it's their DB, not mine. So
> I can't modify the way the DB was created in the first place, I can only
> work with what I have. And, whatever the solution to this might be, it
> does NOT have to be strictly MySQL, it can also be a PHP solution (which
> is why I'm sending it there as well.) So, having said that, consider
> the following table:
>
> +-------+-----+-----+---+
> | 00001 | 123 | 0.0 | C |
> | 00001 | 234 | 0.1 | D |
> | 00001 | 345 | 0.0 | D |
> | 00001 | 456 | 0.1 | C |
> | 00001 | 567 | 0.1 | G |
> | 00002 | 123 | 0.0 | C |
> | 00002 | 234 | 0.1 | D |
> | 00002 | 345 | 0.0 | D |
> | 00003 | 234 | 0.1 | D |
> | 00003 | 345 | 0.0 | D |
> | 00003 | 123 | 0.0 | C |
> | 00003 | 456 | 0.1 | C |
> | 00003 | 567 | 0.1 | G |
> | 00004 | 123 | 0.0 | C |
> | 00004 | 234 | 0.1 | D |
> | 00004 | 345 | 0.0 | D |
> +-------+-----+-----+---+
>
> mysql> select * from table where id='00001';
> +-------+-----+-----+---+
> | 00001 | 123 | 0.0 | C |
> | 00001 | 234 | 0.1 | D |
> | 00001 | 345 | 0.0 | D |
> | 00001 | 456 | 0.1 | C |
> | 00001 | 567 | 0.1 | G |
> +-------+-----+-----+---+
>
> Now, I have to find other IDs that match the above result. In the
> table, that would be ID '00003' (and in the entire DB, there may be
> others as well - I need to find all those IDs.) But, notice how ID 0003
> isn't in the same order as ID 00001, but the data is still the same.
>
> So how do I efficiently search through the DB to find other IDs that
> matches the one I need? I can't imagine doing a for loop selecting each
> ID and comparing their result to the one I'm starting with. If the DB
> contains thousands upon thousands of rows, that might take a very long
> time.
>
> Open to suggestions.
>

Just because someone hands you a set of data to *start* with, does not
mean that you must only use that data to *work* with.

You should be able create additional tables derived from the original
data and work with those as part of your analysis project. No
modification of the original *tables* will be required.

But this would indeed be much easier to talk about if your columns had
names.

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Re: MySQL select matching

On 7/19/2010 10:48 AM, Michael Dykman wrote:
> Not quite sure what the question is.
>
> from:
>
>> mysql> select * from table where id='00001';
>> +-------+-----+-----+---+
>> | 00001 | 123 | 0.0 | C |
>> | 00001 | 234 | 0.1 | D |
>> | 00001 | 345 | 0.0 | D |
>> | 00001 | 456 | 0.1 | C |
>> | 00001 | 567 | 0.1 | G |
>> +-------+-----+-----+---+
>>
> How do we deduce that you would want ID '00003' ?
>
> This conversation would be easier if we gave names to those columns..
>

I didn't think it mattered, but the ID that I'm starting with (in
this case '00001') is the user id currently searching the DB. Basically
I take the user id and collect the initial data set I need to compare
against.

As for names on the columns, ok:

+-------+-----+-----+---+
| uid | set | dec | l |
+-------+-----+-----+---+
| 00001 | 123 | 0.0 | C |
| 00001 | 234 | 0.1 | D |
| 00001 | 345 | 0.0 | D |
| 00001 | 456 | 0.1 | C |
| 00001 | 567 | 0.1 | G |
+-------+-----+-----+---+




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Re: MySQL select matching

On 7/19/2010 11:08 AM, Shawn Green (MySQL) wrote:
> Just because someone hands you a set of data to *start* with, does not
> mean that you must only use that data to *work* with.
>
> You should be able create additional tables derived from the original
> data and work with those as part of your analysis project. No
> modification of the original *tables* will be required.
>
> But this would indeed be much easier to talk about if your columns had
> names.
>

And that's okay, having to create temporary tables to work with.
What I meant was, I can't go back to the client and tell them their
schema is really messed up and to store their data differently. I know
I can create additional tables to work with, and ultimately that may
indeed happen.

A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Re: MySQL select matching

Ashley,

> I can't go back to the client and tell them their schema
> is really messed up and to store their data differently.

You can hope that's not not necessary in order to deliver the requested
query, but it's a bad mistake to rule it out altogether, since it often
happens that accomplishing the requested query efficiently requires DDL
changes.

I suggest too that you're more likely to get good help if you describe
the problem with a set of Create Table and Insert statements than with
truncated results of Describe.

PB

-----

On 7/19/2010 12:29 PM, Ashley M. Kirchner wrote:
> On 7/19/2010 11:08 AM, Shawn Green (MySQL) wrote:
>> Just because someone hands you a set of data to *start* with, does
>> not mean that you must only use that data to *work* with.
>>
>> You should be able create additional tables derived from the original
>> data and work with those as part of your analysis project. No
>> modification of the original *tables* will be required.
>>
>> But this would indeed be much easier to talk about if your columns
>> had names.
>>
>
> And that's okay, having to create temporary tables to work with.
> What I meant was, I can't go back to the client and tell them their
> schema is really messed up and to store their data differently. I
> know I can create additional tables to work with, and ultimately that
> may indeed happen.
>
> A
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.441 / Virus Database: 271.1.1/3015 - Release Date: 07/19/10 06:36:00
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL select matching

On Mon, 19 Jul 2010 10:36:40 -0600
"Ashley M. Kirchner" wrote:


> mysql> select * from table where id='00001';
> +-------+-----+-----+---+
> | 00001 | 123 | 0.0 | C |
> | 00001 | 234 | 0.1 | D |
> | 00001 | 345 | 0.0 | D |
> | 00001 | 456 | 0.1 | C |
> | 00001 | 567 | 0.1 | G |
> +-------+-----+-----+---+
>
> Now, I have to find other IDs that match the above result. In the
> table, that would be ID '00003' (and in the entire DB, there may be
> others as well - I need to find all those IDs.) But, notice how ID 0003
> isn't in the same order as ID 00001, but the data is still the same.
>
select distinct id from `table` where concat(`b`, `c`, `d`) in (select concat(`b`,`c`,`d` from `table` where id = '0001') AND id != '0001';
(untested)

--
Simcha Younger

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL select matching

--00c09f99e3ff536735048bf42161
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

hello ashley@pcraft.com;
where is the solution:
You have table A, so copy the entire table in another table. Table B.
so Table A=3DTable B (only in data no constrains)
then [id]=3Did you want to search

Untitled

select B.cod,count(*) entrysA from A,B where
A.atrib1=3DB.atrib1 and
A.atrib2=3DB.atrib2 and
A.cod<>B.cod and
A.cod=3D [id]
group by B.cod
having entrysA=3D(select count(*) from A where cod=3D [id])

The first colum are your id=B4s solutions and the second colums the amount =
of
rows per Id.
I hope I help....nice problem... I did it with a friend...
cya man


Roberto Z=E1rate Mendoza
Student of systems engineering
Facultad de Ingenieria Industrial y Sistemas
Universidad Nacional de Ingenieria - Lima, Peru

--00c09f99e3ff536735048bf42161--