Home » SQL & PL/SQL » SQL & PL/SQL » Table Comparison....
Table Comparison.... [message #232529] Fri, 20 April 2007 22:39 Go to next message
neoxman
Messages: 3
Registered: April 2007
Junior Member
Hi guys,

For a query i am dealing with tables of the following schema:
CITY(City, Country)
PK(City)

STUDENT(StuID, StuSurname, StuFirstname, Gender, BornInCity, AttendedSchoolInCity)
PK(StuId)
FK(BornInCity) REFERENCES CITY
FK(AttendedSchoolInCity) REFERENCES CITY

UNIVERSITY(UniCode, UniName, City)
PK(UniCode)
FK(City) REFERENCES CITY

Now i need to be able to view the students which are born in the same country as the university they attend.
I've been at this for hours and i cannot figure out how to compare the tables in the correct way.
Any help would be appreciated.

[Updated on: Fri, 20 April 2007 23:13]

Report message to a moderator

Re: Table Comparison.... [message #232530 is a reply to message #232529] Fri, 20 April 2007 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Please post all you have tried & explain how/why they failed.
Please be sure to read & follow the #1 STICKY post by using "code tags".
Re: Table Comparison.... [message #232531 is a reply to message #232529] Fri, 20 April 2007 23:26 Go to previous messageGo to next message
neoxman
Messages: 3
Registered: April 2007
Junior Member
Ok this is the query that has gotten me closest to what i want:

Prompt Query 8;
SELECT DISTINCT s.StuSurname, s.StuFirstname
FROM STUDENT s, STUDENT s2, UNIVERSITY u
WHERE s.BornInCity = s2.AttendedSchoolInCity;


This gets me the two results i am after but it also includes another result for a student who attended a university in a different country.

Here are my create table and insert statements if it helps.

create table CITY (
City CHAR(14),
Country CHAR(10),
PRIMARY KEY (City)
);

create table STUDENT (
StuID NUMBER(3) CONSTRAINT CHK_STUID CHECK(StuID BETWEEN 1 and 800),
StuSurname CHAR(8),
StuFirstname CHAR(6),
Gender CHAR(1) CONSTRAINT CHK_GENDER CHECK(Gender IN('M', 'F')),
BornInCity CHAR(14),
AttendedSchoolInCity CHAR(14),
CONSTRAINT CHK_NAME UNIQUE(StuFirstname, StuSurname),
PRIMARY KEY (StuID),
FOREIGN KEY (BornInCity) REFERENCES CITY,
FOREIGN KEY (AttendedSchoolInCity) REFERENCES CITY
);

create table UNIVERSITY (
UniCode CHAR(7),
UniName CHAR(40),
City CHAR(14),
PRIMARY KEY (UniCode),
FOREIGN KEY (City) REFERENCES CITY
);

insert into CITY VALUES('Melbourne', 'Australia');
insert into CITY VALUES('Sydney', 'Australia');
insert into CITY VALUES('Massachusetts', 'USA');
insert into CITY VALUES('Oxford', 'England');
insert into CITY VALUES('Beijing', 'China');
insert into CITY VALUES('Mumbai', 'India');
insert into CITY VALUES('London', 'England');
insert into CITY VALUES('New York', 'USA');
insert into CITY VALUES('Shanghai', 'China');

insert into UNIVERSITY VALUES('UNIMELB', 'University of Melbourne', 'Melbourne');
insert into UNIVERSITY VALUES('SWIN', 'Swinburne University of Technology', 'Melbourne');
insert into UNIVERSITY VALUES('UNISYD', 'University of Sydney', 'Sydney');
insert into UNIVERSITY VALUES('MIT', 'Massachusetts Institute of Technology', 'Massachusetts');
insert into UNIVERSITY VALUES('OXFORD', 'University of Oxford', 'Oxford');
insert into UNIVERSITY VALUES('PEKUNI', 'Peking University', 'Beijing');

insert into STUDENT VALUES(100, 'Apple', 'Adam', 'M', 'Melbourne', 'Sydney');
insert into STUDENT VALUES(200, 'Banana', 'Beth', 'F', 'London', 'London');
insert into STUDENT VALUES(300, 'Carrot', 'Carol', 'F', 'Sydney', 'New York');
insert into STUDENT VALUES(400, 'Donut', 'Dave', 'M', 'Mumbai', 'Melbourne');
insert into STUDENT VALUES(500, 'Eggplant', 'Errol', 'M', 'Beijing', 'Melbourne');

[Updated on: Fri, 20 April 2007 23:27]

Report message to a moderator

Re: Table Comparison.... [message #232532 is a reply to message #232529] Fri, 20 April 2007 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Since the CITY table is the only table which contains country & the requirements include country, I'd say that the table CITY must be part of the correct SQL statement; which does not exist in your 1 example.
Re: Table Comparison.... [message #232568 is a reply to message #232529] Sat, 21 April 2007 05:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First, try to show columns from STUDENT s2 to understand, what are you querying for. Also joining UNIVERSITY u without any condition leads to cartesian product, which leads only to multiplying rows by the cardinality of UNIVERSITY table.

Then, what about this?
SELECT DISTINCT s.StuSurname, s.StuFirstname
FROM STUDENT s, CITY c1, CITY c2
WHERE s.BornInCity = c1.City 
  AND s.AttendedSchoolInCity = c2.City
  AND c1.Country = c2.Country;

By the way, do you not want to know which university the student attends? As the tables you provided do not contain this information.

[Edit: corrected the query]

[Updated on: Sat, 21 April 2007 05:13]

Report message to a moderator

Re: Table Comparison.... [message #232572 is a reply to message #232529] Sat, 21 April 2007 06:13 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
There is probably a much easier way but.....
I think the following will solve it:
select born.name,
       born.country
from   (
         select student.stusurname name,
                student.stuid stuid,
                city.country country
         from   student,
                city
         where  student.bornincity=city.city
       ) born,
       (
         select student.stuid stuid,
                city.country country
         from   student,
                city
         where  student.attendedschoolincity=city.city
       ) school 
where  born.country=school.country and
       born.stuid=school.stuid;



If someone has a easier solution I'm verry interested since I'm learning SQL also. Smile
icon7.gif  Re: Table Comparison.... [message #232576 is a reply to message #232572] Sat, 21 April 2007 06:42 Go to previous messageGo to next message
neoxman
Messages: 3
Registered: April 2007
Junior Member
Thank you so much martijn! That worked perfectly! Very Happy
Re: Table Comparison.... [message #232872 is a reply to message #232529] Mon, 23 April 2007 13:56 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
The following is a little simpler.

select s.StuSurname, s.StuFirstname,c1.country
from student s, city c1, city c2
where s.AttendedSchoolInCity = c1.city
and s.BornInCity = c2.city
and c1.country = c2.country;

Re: Table Comparison.... [message #232874 is a reply to message #232872] Mon, 23 April 2007 14:22 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
That indeed is simpler.
I always forget the possibilty of using a table twice. I somehow have the idea that it's not very cost-effective. But..... your query is way more cost effective then mine. Embarassed

thanks for sharing this.
Previous Topic: what is the differnece between MS-access and oracle databases
Next Topic: Triple Outer Join with WHERE and ORDER conditions
Goto Forum:
  


Current Time: Wed Dec 07 20:04:49 CST 2016

Total time taken to generate the page: 0.11488 seconds