Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query required for self join
SQL Query required for self join [message #364926] Wed, 10 December 2008 08:43 Go to next message
windzofdesire
Messages: 2
Registered: December 2008
Junior Member
I have the following three tables:-

City(name, province, country)
Hall(name, city, capacity) (here city refers to the name attribute of City)
Show(title, artist, hall, attendance) (here hall refers to the name attribute of Hall)

I need a query and the relational algebra that does the following:-
Find artists who performed in two different cities with the same name, but located in different provinces of the same country.

Can you please help me out with this.
Re: SQL Query required for self join [message #364929 is a reply to message #364926] Wed, 10 December 2008 08:47 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi,
Welcome to the Forums. I have moved this thread to Homework as the tone of the post suggests that it is exactly that. In order for us to help you, please provide much more information. Best bet is for you to check out the posting guidelinesParticularly the bit on how to get a quick answer. Also, please post what you have already tried. We are happy to help, but we won't do your work for you and we expect you to put in some effort of your own.

Cheers
Re: SQL Query required for self join [message #364938 is a reply to message #364929] Wed, 10 December 2008 09:20 Go to previous message
windzofdesire
Messages: 2
Registered: December 2008
Junior Member
This is what I tried... but don't know if its correct:-

SELECT S1.Artist
FROM Show S1, Show S2,
Hall H1, Hall H2,
City C1, City C2
WHERE S1.Artist = S2.Artist
AND S1.Hall = H1.Name
AND S2.Hall = H2.Name
AND H1.City = C1.Name
AND H2.City = C2.Name
AND H1.City = H2.City
AND C1.Province <> C2.Province
AND C1.Country = C2.Country

Your help will be much appreciated
Previous Topic: How to create Text file on local machine
Next Topic: Displaying the foreign key details in oracle 9i
Goto Forum:
  


Current Time: Sun Dec 04 12:53:08 CST 2016

Total time taken to generate the page: 0.26616 seconds