join tables: newbie question

Hi,
I have a table STATES

ID NAME
----------------------------
0 AL
1 CA
2 DE
3 WY


and a table ROOTS

NAME MOM_STATE_ID DAD_STATE_ID
-------------------------------------------------------------------------------
John 0 0
Jeff 0 1
Jack 1 2
Jim 3 2


What I need is a query to select Name, Mom_state_name and
Dad_state_name

John AL AL
Jeff AL CA
Jack CA DE
Jim WY DE

I'm sorry fro such a boring question and example: I'm sure there is an
appropriate terminology for that, but I'm not expert in SQL at all :)

Thanks
Lorenzo

lbusoni wrote:
> Hi,
> I have a table STATES
>
> ID NAME
> ----------------------------
> 0 AL
> 1 CA
> 2 DE
> 3 WY
>
>
> and a table ROOTS
>
> NAME MOM_STATE_ID DAD_STATE_ID
> -------------------------------------------------------------------------------
> John 0 0
> Jeff 0 1
> Jack 1 2
> Jim 3 2
>
>
> What I need is a query to select Name, Mom_state_name and
> Dad_state_name
>
> John AL AL
> Jeff AL CA
> Jack CA DE
> Jim WY DE
>
> I'm sorry fro such a boring question and example: I'm sure there is an
> appropriate terminology for that, but I'm not expert in SQL at all :)
>
> Thanks
> Lorenzo

SELECT
r.name,
m.name Mom_State,
d.name Dad_State
FROM roots r
JOIN states m ON r.mom_state_id = m.id
JOIN states d ON r.dad_state_id = d.id

On Mar 1, 11:34 am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> lbusoni wrote:
> > Hi,
> > I have a table STATES
>
> > ID NAME
> > ----------------------------
> > 0 AL
> > 1 CA
> > 2 DE
> > 3 WY
>
> > and a table ROOTS
>
> > NAME MOM_STATE_ID DAD_STATE_ID
> > -------------------------------------------------------------------------------
> > John 0 0
> > Jeff 0 1
> > Jack 1 2
> > Jim 3 2
>
> > What I need is a query to select Name, Mom_state_name and
> > Dad_state_name
>
> > John AL AL
> > Jeff AL CA
> > Jack CA DE
> > Jim WY DE
>
> > I'm sorry fro such a boring question and example: I'm sure there is an
> > appropriate terminology for that, but I'm not expert in SQL at all :)
>
> > Thanks
> > Lorenzo
>
> SELECT
> r.name,
> m.name Mom_State,
> d.name Dad_State
> FROM roots r
> JOIN states m ON r.mom_state_id = m.id
> JOIN states d ON r.dad_state_id = d.id

Thanks a lot!

lbusoni wrote:
> Hi,
> I have a table STATES
>
> ID NAME
> ----------------------------
> 0 AL
> 1 CA
> 2 DE
> 3 WY
>
>
> and a table ROOTS
>
> NAME MOM_STATE_ID DAD_STATE_ID
> -------------------------------------------------------------------------------
> John 0 0
> Jeff 0 1
> Jack 1 2
> Jim 3 2
>
>
> What I need is a query to select Name, Mom_state_name and
> Dad_state_name
>
> John AL AL
> Jeff AL CA
> Jack CA DE
> Jim WY DE
>
> I'm sorry fro such a boring question and example: I'm sure there is an
> appropriate terminology for that, but I'm not expert in SQL at all :)
>
> Thanks
> Lorenzo
>
>
>
>
hmmm.. looks like a homework question to me...