List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 11 2012 10:51pm
Subject:Re: The most elegant/efficient way to pull this SQL query
View as plain text  
On 4/11/2012 1:30 PM, Haluk Karamete wrote:
> 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)________

select b.peopleID, concat('(',p.fname,,')'), b.stateID, 
concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;

PB

-----

>
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