-3

Please explain the SQL query given below. I m new with SQL and struggling with Join Statements. Thanks in advance.

select a.ename    as attendee
,      t.ename    as trainer
from   employees     t
       join
       offerings     o on  (o.trainer = t.empno)
       join
       registrations r using (course, begindate)
       join
       employees     a on (r.attendee = a.empno)
 where  course = 'SQL';
Abhinav
  • 3
  • 2

3 Answers3

2

Joins are quite nicely explained here:

http://www.postgresql.org/docs/current/static/tutorial-join.html

  • I was expecting an explanation, how Join statements are used in above query able to connect three different tables. – Abhinav Apr 02 '12 at 16:11
  • @Abhinav: did you read the tutorial? It does explain how joins work and therefor it explains the above statement. –  Apr 02 '12 at 16:13
0

Just watch sql tutorials on youtube.com.

http://www.youtube.com/results?search_query=mysql+join&oq=mysql+join

or read the tutorials at w3schools.com http://www.w3schools.com/sql/sql_join_inner.asp

Larry Battle
  • 9,008
  • 4
  • 41
  • 55
0

This query gets the names of all attendees and their trainer's name for all 'SQL' courses. You have a database with at least 3 tables: employees, offerings and registrations. An Employee can register for a course offering and the trainer of a course offering is also an Employee. To register for an Offering, you must provide the course name and the begin date of that course.

employees t join offerings o on (o.trainer = t.empno)

Here employees are related to course offerings using the trainer nr and the employee number. To make it clear that here we are only interested in those Employees who are trainer, the alias name "t" was used for the Employee table. "o" is the alias name for the Offerings table. This join gives us all courses for which the trainer is known and is an employee.

join registrations r using (course, begindate)

Here registrations are related to the course offerings using the course name and the begindate of the course offering. The previous line is equal to:

join registrations r on o.course = r.course AND o.begindate = r.begindate


join employees a on (r.attendee = a.empno)

Here employees are related to registrations using the attendee nr and the employee number. To make it clear that here we are only interested in those Employees who are attendee of a course, the alias name "a" was used for the Employee table.

where  course = 'SQL';

Up till now we have a long list of all trainer-employees with ethe courses they trained, the registrations and their attendee-empleyees. Of that long list we are only interested in those rows where the course name equals "SQL".

Wim
  • 1,058
  • 8
  • 10