Example taken from
Beginning SQL queries- From novice to professional by Clare Churcher(sorry about the long example script)
I have 3 tables:
Member,
Entry,
Tournament (DDL/DML scripts attached below).
Member and Tournament tables have no common columns . Member.Membertype and Tournament.tourtype are different columns despite similarity in its contents
Table 1. Member
MEMBERID LASTNAME FIRSTNAME MEMBERTYPE
--------- -------------------- -------------------- --------------
118 McKenzie Melissa Junior
138 Stone Michael Senior
153 Nolan Brenda Senior
176 Branch Helen Social
178 Beck Sarah Social
228 Burton Sandra Junior
235 Cooper William Senior
239 Spence Thomas Senior
258 Olson Barbara Senior
286 Pollard Robert Junior
290 Sexton Thomas Senior
323 Wilcox Daniel Senior
331 Schmidt Thomas Senior
332 Bridges Deborah Senior
339 Young Betty Senior
414 Gilmore Jane Junior
415 Taylor William Senior
461 Reed Robert Senior
469 Willis Carolyn Junior
487 Kent Susan Social
20 rows selected.
Table 2: Entry
MEMBERID TOURID YEAR
---------- ---------- ----------
118 24 2005
228 24 2006
228 25 2006
228 36 2006
235 38 2004
235 38 2006
235 40 2005
235 40 2006
239 25 2006
239 40 2004
258 24 2005
258 38 2005
286 24 2004
286 24 2005
286 24 2006
415 24 2006
415 25 2004
415 36 2005
415 36 2006
415 38 2004
415 38 2006
415 40 2004
415 40 2005
415 40 2006
24 rows selected.
Table 3: Tournament
TOURID TOURNAME TOURTYPE
-------- -------------------- --------------
24 Leeston Social
25 Kaiapoi Social
36 WestCoast Open
38 Canterbury Open
40 Otago Open
My requirement:+
I need to find the names of everyone who entered Open tournament (Tournament. Tourtype = Open). So i wrote the below query
select distinct m.memberid, m.lastname, m.firstname, t.tourtype
from
member m inner join entry e on (m.memberid=e.memberid)
inner join tournament t on (e.tourid=t.tourid)
and upper(t.tourtype)='OPEN'
order by m.lastname;
This gives me the correct result.
MEMBERID LASTNAME FIRSTNAME TOURTYPE
-------- ---------- ---------- ------------------
228 Burton Sandra Open
235 Cooper William Open
258 Olson Barbara Open
239 Spence Thomas Open
415 Taylor William Open
But, is there way i can write this SQL using EXISTS operator?
The DDL and DML for tables and its data
CREATE TABLE Type(
Type VARCHAR2(20) Primary Key,
Fee number)
/
CREATE TABLE Member(
MemberID NUMBER Primary Key,
LastName VARCHAR2(20),
FirstName VARCHAR2(20),
MemberType VARCHAR2(20) constraint fk1_member References type(type),
Phone VARCHAR2(20), Handicap NUMBER, JoinDate DATE, Coach NUMBER, Team
VARCHAR2(20), Gender VARCHAR2(1))
/
CREATE TABLE Tournament(
TourID NUMBER Primary Key,
TourName VARCHAR2(20),
TourType VARCHAR2(20))
/
CREATE TABLE Entry(
MemberID NUMBER constraint fk1_entry References Member(memberid),
TourID NUMBER constraint fk2_entry References Tournament(tourid), Year
NUMBER,
constraint pk_entry Primary Key (MemberID, TourID, Year))
/
Insert into Type values ('Junior',150)
/
Insert into Type values ('Senior',300)
/
Insert into Type values ('Social',50)
/
Insert into Member values
(118,'McKenzie','Melissa','Junior','963270',30,null,null,null,'F')
/
Insert
into Member values
(138,'Stone','Michael','Senior','983223',30,null,null,null,'M')
/
Insert
into Member values
(153,'Nolan','Brenda','Senior','442649',11,null,null,'TeamB','F')
/
Insert
into Member values
(176,'Branch','Helen','Social','589419',null,null,null,null,'F')
/
Insert
into Member values
(178,'Beck','Sarah','Social','226596',null,null,null,null,'F')
/
Insert
into Member values
(228,'Burton','Sandra','Junior','244493',26,null,null,null,'F')
/
Insert
into Member values
(235,'Cooper','William','Senior','722954',14,null,null,'TeamB','M')
/
Insert into Member values
(239,'Spence','Thomas','Senior','697720',10,null,null,null,'M')
/
Insert
into Member values
(258,'Olson','Barbara','Senior','370186',16,null,null,null,'F')
/
Insert
into Member values
(286,'Pollard','Robert','Junior','617681',19,null,null,'TeamB','M')
/
Insert into Member values (290,'Sexton
','Thomas','Senior','268936',26,null,null,null,'M')
/
Insert into Member
values (323,'Wilcox','Daniel','Senior','665393',3,null,null,'TeamA','M')
/
Insert into Member values
(331,'Schmidt','Thomas','Senior','867492',25,null,null,null,'M')
/
Insert
into Member values
(332,'Bridges','Deborah','Senior','279087',12,null,null,null,'F')
/
Insert
into Member values
(339,'Young','Betty','Senior','507813',21,null,null,'TeamB','F')
/
Insert
into Member values
(414,'Gilmore','Jane','Junior','459558',5,null,null,'TeamA','F')
/
Insert
into Member values
(415,'Taylor','William','Senior','137353',7,null,null,'TeamA','M')
/
Insert
into Member values
(461,'Reed','Robert','Senior','994664',3,null,null,'TeamA','M')
/
Insert
into Member values
(469,'Willis','Carolyn','Junior','688378',29,null,null,null,'F')
/
Insert
into Member values
(487,'Kent','Susan','Social','707217',null,null,null,null,'F')
/
Insert into Tournament values (24,'Leeston','Social')
/
Insert into Tournament values (25,'Kaiapoi','Social')
/
Insert into Tournament values (36,'WestCoast','Open')
/
Insert into Tournament values (38,'Canterbury','Open')
/
Insert into Tournament values (40,'Otago','Open')
/
Insert into Entry values (118,24,2005)
/
Insert into Entry values (228,24,2006)
/
Insert into Entry values (228,25,2006)
/
Insert into Entry values (228,36,2006)
/
Insert into Entry values (235,38,2004)
/
Insert into Entry values (235,38,2006)
/
Insert into Entry values (235,40,2005)
/
Insert into Entry values (235,40,2006)
/
Insert into Entry values (239,25,2006)
/
Insert into Entry values (239,40,2004)
/
Insert into Entry values (258,24,2005)
/
Insert into Entry values (258,38,2005)
/
Insert into Entry values (286,24,2004)
/
Insert into Entry values (286,24,2005)
/
Insert into Entry values (286,24,2006)
/
Insert into Entry values (415,24,2006)
/
Insert into Entry values (415,25,2004)
/
Insert into Entry values (415,36,2005)
/
Insert into Entry values (415,36,2006)
/
Insert into Entry values (415,38,2004)
/
Insert into Entry values (415,38,2006)
/
Insert into Entry values (415,40,2004)
/
Insert into Entry values (415,40,2005)
/
Insert into Entry values (415,40,2006)
/