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
-----
>
