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.

Any solution provided to this will return different results from the query you posted, because you use *. You should totally avoid that for this very reason, and enumerate all the fields you are interested at. — Alex Bagnolini
– Alex Bagnolini, Commented Nov 19, 2009 at 12:58

Andomar · Accepted Answer · 2009-11-19 12:50:34Z

6

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.

answered Nov 19, 2009 at 12:50

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kristof Over a year ago

it would return multiple records for customer where there is more that one match in the join. But that is probably what we want here if there many appointments meeting the data criteria. It is just that the original query returns only one record per customer

Alex Bagnolini Over a year ago

Wonderful solution. Nice to see (not) they went for CustomerID in a table and CxId in another one.

Alex Bagnolini Over a year ago

You either have to submit a grouping rule, or handle it in business level if you want it to return a single customer value.

Hugo Assanti · Accepted Answer · 2009-11-19 12:51:18Z

1

Read this http://www.w3schools.com/Sql/sql_join.asp

answered Nov 19, 2009 at 12:51

Hugo Assanti

4311 gold badge8 silver badges16 bronze badges

Collectives™ on Stack Overflow

Using JOINS in MySQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related