Using JOINS in MySQL

Question

I have this query which works perfectly:

SELECT *
FROM Customer
WHERE SacCode IN
(
    SELECT SacCode
    FROM SacCode
    WHERE ResellerCorporateID = 392
    ORDER BY SacCode
)
AND CustomerID IN
(
    SELECT CxID
    FROM CustAppointments
    WHERE AppRoomID IN
    (
        SELECT AppRoomID
        FROM ClinicRooms
        WHERE ClinID IN
        (
                SELECT ClinID
                FROM AppClinics
                WHERE ClinDate >='20090101'
                AND ClinDate <='20091119'
        )
    )
)

However, I need to see the value of ClinDate (inside the last nested query) so I've been told I need to rework the query using JOINS.

I have no idea how, can someone help please?

Thanks.

Answer 1

Here's a start:

SELECT     *
FROM       Customer c
INNER JOIN CustAppointments ca ON ca.CxId = c.CustomerID
INNER JOIN ClinicRooms cr ON cr.AppRoomID = ca.AppRoomID
INNER JOIN AppClinics ac ON ac.ClinID = cr.ClinID
WHERE      ap.ClinDate BETWEEN '20090101' AND '20091119'
AND        SacCode IN (SELECT sc.SacCode 
                       FROM SacCode sc 
                       WHERE sc.ResellerCorporateID = 392)

This will allow you to select columns from AppClinics.

Answer 2

Read this http://www.w3schools.com/Sql/sql%5Fjoin.asp