List:General Discussion« Previous MessageNext Message »
From:(Hal Date:April 11 2012 10:04pm
Subject:Re: The most elegant/efficient way to pull this SQL query
View as plain text  
;>>> 2012/04/11 11:30 -0700, Haluk Karamete >>>>
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs

____peopleID_______stateID
____1______________1____
____2______________4____
____3______________5____


people table is like this;

___peopleID_____FName_____
___1____________joe________
___2____________bob_______
___3____________charlie_____


and finally the state table goes like this;

___stateID_____State_______
_______1_______california____
_______2_______new york____
_______3_______washington__
_______4_______texas_______
_______5_______florida______


What's the most straightforward way to achieve the following view with one
SQL statement?


____peopleID__________stateID_____________
____1_(joe)___________1__(california)_____
____2_(bob)___________4__(texas)__________
____3_(charlie)_______5__(florida)________
<<<<<<<<
Look at Stephen Tu s original post under the subject "forcing mysql to use batched key
access (BKA) optimization for joins". That his query solves a problem very much like
yours--but use explicit JOINing, not implicit. He also uses an unhappy style of making
every field name in the database unique. Joining is easier if the fields to be joined on
have same names.

Thread
The most elegant/efficient way to pull this SQL queryHaluk Karamete11 Apr
  • Re: The most elegant/efficient way to pull this SQL queryPeter Brawley11 Apr
    • Re: The most elegant/efficient way to pull this SQL queryhsv12 Apr
      • Re: The most elegant/efficient way to pull this SQL queryHaluk Karamete12 Apr
        • Re: The most elegant/efficient way to pull this SQL queryhsv13 Apr
  • Re: The most elegant/efficient way to pull this SQL queryhsv12 Apr