Prev: Copy documents for another site.
Next: eval and HEREDOC
From: "Ashley M. Kirchner" on 19 Jul 2010 12:36

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.
From: Michael Dykman on 19 Jul 2010 12:48
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..

- michael dykman


On Mon, Jul 19, 2010 at 12:36 PM, Ashley M. Kirchner <ashley(a)pcraft.com> 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.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdykman(a)gmail...com
>
>



--
 - michael dykman
 - mdykman(a)gmail.com

 May the Source be with you.
From: "Ashley M. Kirchner" on 19 Jul 2010 13:27
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 |
+-------+-----+-----+---+



From: Simcha Younger on 21 Jul 2010 02:55
On Mon, 19 Jul 2010 10:36:40 -0600
"Ashley M. Kirchner" <ashley(a)pcraft.com> 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 <simcha(a)syounger.com>
 | 
Pages: 1
Prev: Copy documents for another site.
Next: eval and HEREDOC