New to SQL. Query SQL database using info across three tables

This is using phpMyAdmin.

I need to find the contact information for Subscribers who have pending Orders on November 15th. Their contact information is stored in a table called Subscribers, and the primary key is UID (User ID). The Subscriptions Table has a primary key called SID (Subscriptions ID). The Subscriptions table also stores the UID for each Subscription. However, the Orders table is where the Date is stored, and this table stores the SID but not the UID, so I can't directly JOIN Orders with Subscribers.

I have to JOIN Orders with Subscriptions on SID where the Orders Date is 11-15-10, and then I have to JOIN the resulting table with the Subscribers table on UID.

I'm currently trying this:

SELECT * FROM Subscribers
RIGHT JOIN (Orders a, Subscriptions b, Subscribers c)
ON (a.SID = b.SID AND b.UID = c.UID)
WHERE a.Date = '2010-11-01'

This is开发者_如何学Go causing a massive lag followed by Gateway Timeout.

This is a classic case of knowing what to do, but not knowing how to do it. Any help would be greatly appreciated. Thanks!

You could try this:

SELECT
    scrb.*
FROM
    Subscribers scrb
WHERE
    scrb.UID in (
        SELECT DISTINCT
            scrp.UID
        FROM
            Subscriptions scrp
            INNER JOIN Orders ordr ON
                ordr.SID = scrp.SID
        WHERE
            ordr.Date = STR_TO_DATE('2010-11-01')
    )

Not sure if you're going to have a big performance improvement though... Maybe your tables miss a better indexing strategy...?

In fact, you should try executing just the inner query (SELECT DISTINCT scrp.UID...) first... If it is too slow, I would guess your problem is on the Orders.Date field - a full scan over that table probably has a high performance cost.

Why do you join Subscribers to Subscribers? SELECT * FROM Subscribers ... JOIN ... Subscribers c)

Given the limited amount we know about your schema, it seems like you'd do better with an INNER JOIN, which will filter records for you, and @seriyPS is right about the redundant Subscribers table - currently, this query as written is performing a CROSS JOIN, joining all Subscribers to every result of Subscriber joined to Subscription joined to Order...

Is there a reason why this won't work?

SELECT a.*
FROM Subscribers a
INNER JOIN Subscriptions b ON a.UID = b.UID
INNER JOIN Orders c ON b.SID = c.SID
WHERE c.Date = '2010-11-01'