List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:July 19 2010 5:08pm
Subject:Re: MySQL select matching
View as plain text  
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
Thread
MySQL select matchingAshley M. Kirchner19 Jul
  • Re: MySQL select matchingMichael Dykman19 Jul
    • Re: [MySQL] Re: MySQL select matchingAshley M. Kirchner19 Jul
    • Re: MySQL select matchingRoberto Z├írate Mendoza22 Jul
  • Re: MySQL select matchingMySQL)19 Jul
    • Re: [MySQL] Re: MySQL select matchingAshley M. Kirchner19 Jul
      • Re: [MySQL] Re: MySQL select matchingPeter Brawley19 Jul
  • Re: [PHP] MySQL select matchingSimcha Younger21 Jul