How to convert Sql query with inner join statement to sql query with Where statement(no inner join in statement)

Question

I have generated the following sql query code in access using the Qbe and converting it to sql.However, i want proof that i did without help so i intend to take out all inner join statements and replace them with the WHERE statement. How do i work it out. Please explain and provide answer. thank you.

SQL Query:

SELECT Entertainer.EntertainerID, Entertainer.FirstName, Entertainer.LastName, 
       Booking.CustomerID, Booking.EventDate, BookingDetail.Category, BookingDetail.Duration, 
       Speciality.SpecialityDescription, EntertainerSpeciality.EntertainerSpecialityCost

FROM (Entertainer INNER JOIN (Booking INNER JOIN BookingDetail ON 
                              Booking.BookingID=BookingDetail.BookingID) ON   
      Entertainer.EntertainerID=BookingDetail.EntertainerID) 
INNER JOIN (Speciality INNER JOIN EntertainerSpeciality ON 
            Speciality.SpecialityID=EntertainerSpeciality.SpecialityID) ON 
Entertainer.EntertainerID=EntertainerSpeciality.EntertainerID

WHERE (((Entertainer.EntertainerID)=[Enter EntertainerID]));

Answer 1

It's a strange thing to want to do (as commenters say, INNER JOIN is usually better, and applying a mechanical transformation proves nothing), but not difficult. Each INNER JOIN can be mechanically transformed into a WHERE as follows:

instead of each 2-way inner join A INNER JOIN B ON (cond1) WHERE (cond2)

rewrite it as A, B WHERE (cond1) AND (cond2)

whatever the tables A and B and the conditions cond1 and cond2 might be.

Answer 2

That is the weirdest JOIN statement I've seen to date. Here's your query converted to ANSI-89 syntax:

SELECT e.entertainerid, 
       e.firstname, 
       e.lastname, 
       b.customerid, 
       b.eventdate, 
       bd.category, 
       bd.duration, 
       s.specialitydescription, 
       es.entertainerspecialitycost
  FROM Entertainer e,
       BookingDetail bd,
       Booking b,
       EntertainerSpeciality es,
       Speciality s
 WHERE e.entertainerid = bd.entertainerid
   AND b.bookingid = bd.bookingid
   AND e.entertainerid = es.entertainerid
   AND s.specialityid = es.specialityid
   AND e.entertainerid = [Enter EntertainerID]

Here your original query with the syntax cleaned up - it should help make it easier to see the common information:

SELECT e.entertainerid, 
       e.firstname, 
       e.lastname, 
       b.customerid, 
       b.eventdate, 
       bd.category, 
       bd.duration, 
       s.specialitydescription, 
       es.entertainerspecialitycost
  FROM ENTERTAINER e
  JOIN BOOKINGDETAIL bd ON bd.entertainerid = e.entertainerid
  JOIN BOOKING b ON b.bookingid = bd.bookingid
  JOIN ENTERTAINERSPECIALITY es ON es.entertainerid = e.entertainerid
  JOIN SPECIALITY s ON s.specialityid = es.specialityid
 WHERE e.entertainerid = ?

The difference is the ANSI-89 syntax includes the join criteria in the WHERE clause, along with the actual filter criteria. To highlight, ANSI-92:

  FROM ENTERTAINER e
  JOIN BOOKINGDETAIL bd ON bd.entertainerid = e.entertainerid

...vs ANSI-89:

  FROM ENTERTAINER e,
       BOOKINGDETAIL bd
       ,... -- omitted for purpose of example
 WHERE e.entertainerid = bd.entertainerid

ANSI-92 syntax is preferred:

• ANSI-89 didn't have consistently implemented LEFT JOIN syntax in various databases, so statements were that portable
• ANSI-92 provides more powerful JOINs (IE: x ON x.id = y.id AND x.col IS NOT NULL)
• ANSI-92 is easier to read, separating the join criteria from the actual filter criteria