Linking the same table twice

[rastkocvetkovic]

I'm interested in SQL query that would link the same table twice.

Let's say I have a list of members like this:

Members:
id
name
surname

Rented:
id
movie
member_who_rented_id
member_who_is_renting_id

Now, both members (member_who_rented_id, member_who_is_renting_id) are linking to the same table named Members. Now I want to get all data in one simple query - both name of the member_who_rented_id and member_who_is_renting_id. How could I perform such a query? Thank you in advance for your answers!

 

[r937]

i can do the sql syntax for you, but i have to admit, i don't understand the difference between "member_who_rented_id" and "member_who_is_renting_id"

i mean, i think i understand it -- you are keeping track of the current renter and the previous one -- but it seems to me that this will require updating current renter into previous renter the next time the movie is rented, which somehow doesn't seem like the best way to do it

but you didn't ask about the design, eh

anyhow, here's your syntax --

select movie
, m1.name as member_who_rented
, m2.name as member_who_is_renting
from rented
inner
join member m1
on member_who_rented_id = m1.id
inner
join member m2
on member_who_is_renting_id = m2.id

rudy

http://rudy.ca/

 

[angiole]

You may access the same table twice by using an alias, as follows:

select a.*
,b.*
,c.*
from rented a
,members b
,members c
where a.member_who_rented_id = b.id and
a.member_who_is_renting_id = c.id

This join syntax is for oracle, but use whatever syntax your DBMS permits.



AA 8~)

 

[ThomVF]

ORACLE solution:

select r.movie
, m1.name || ' ' || m1.surname "Last Renter"
, m2.name || ' ' || m2.surname "Current Renter"
from members m1,
, member m2
, rented r
where m1.id = r.member_who_rented_id
and m2.id = r.member_who_is_renting_id
;

SQLServer solution is the same, with '+' instead of '||' for concatenation.

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com