Home » SQL & PL/SQL » SQL & PL/SQL » Comparing two attributes based on another one
Comparing two attributes based on another one [message #231645] Tue, 17 April 2007 21:05 Go to next message
alo1234
Messages: 2
Registered: April 2007
Junior Member
Hello,

I'm having an assignment which asked me to show the result which display the students who were born in the same continent as the continent of the university they attended located in.

say we have the location table
PK is COUNTRY

INSERT INTO LOCATION(Country, Continent) VALUES ('USA', 'North America);
INSERT INTO LOCATION(Country, Continent) VALUES ('Canada','North America');
INSERT INTO LOCATION(Country, Continent) VALUES ('Vietnam','Asia');
INSERT INTO LOCATION(Country, Continent) VALUES ('Thailand','Asia');
INSERT INTO LOCATION(Country, Continent) VALUES ('UK','Europe');


now we have the student
PK IS StuId
FK is BornInCountry references LOCATION
FK is UNiversityInCountry references LOCATION

INSERT INTO STUDENT(StuId, StuFirstname, StuSurname, Gender, BornInCountry, UniversityCountry) VALUES ('1','Adam','Eva','M','Vietnam','Thailand');

INSERT INTO STUDENT(StuId, StuFirstname, StuSurname, Gender, BornInCountry, UniversityCountry) VALUES ('2','Monkey','Head','M','Thailand','UK');

INSERT INTO STUDENT(StuId, StuFirstname, StuSurname, Gender, BornInCountry, UniversityCountry) VALUES ('3','Hoho','Haha','M','USA','Canada');

INSERT INTO STUDENT(StuId, StuFirstname, StuSurname, Gender, BornInCountry, UniversityInCountry) VALUES ('4','Lala','Lolo','M','Vietnam','Vietnam');

If I try to use the INNER JOIN
ON s.BornInCountry = l.Country
AND s.UniversityInCountry = l.Country

it wont work, it only compare the result based on the country, it didn't know how to read where one country located in one continent.

Anybody have any idea how can I get the student 1,3,4 on my query result?
Thanks alot
Re: Comparing two attributes based on another one [message #231646 is a reply to message #231645] Tue, 17 April 2007 21:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
join against LOCATION table twice
Re: Comparing two attributes based on another one [message #231672 is a reply to message #231645] Wed, 18 April 2007 01:24 Go to previous message
alo1234
Messages: 2
Registered: April 2007
Junior Member
Thank you for the reply but what do you mean by joining it twice?

INNER JOIN LOCATION l ON...

and then another

INNER JOIN LOCATION following that?
Thank you
Previous Topic: procedure within a trigger
Next Topic: why the trigger created with compile errors
Goto Forum:
  


Current Time: Sun Dec 11 02:16:58 CST 2016

Total time taken to generate the page: 0.06042 seconds