CODEWITHSUNDEEP
mysqljoin
GoldenJoe
GoldenJoe

Reputation: 8002

Trouble with triple join

I have three tables:

orders

orderID
userID

users

userID
lastViewedOrderID

votehistory

userID
orderID

I have a PHP function: getOrdersForUser($userID), which should return a set of orderID values with the following conditions:

1) the user has not yet viewed the orders (orders.orderID > users.lastViewedOrderID)

2) the orders were not placed by the user being queried (orders.userID != users.userID)

3) has not been voted on by that user according to the votehistory table (orderID is not in votehistory where userID = [userID value passed in])

So far the best I've come up with is:

SELECT orders.orderID
FROM orders 
JOIN users 
ON users.userID != orders.userID 
JOIN votehistory
ON (votehistory.userID = users.userID) AND (votehistory.orderID != orders.orderID )
WHERE users.userID = [the userID value passed in] 
AND orders.orderID > users.lastViewedOrderID
AND likehistory.orderID != orders.orderID

Unfortunately, this gives me a ton of repeats - the same orderID repeats for each valid row in votehistory. I don't seem to understand how the JOIN process is optimized. Am I supposed to try to reduce the number of rows in the tables being joined through subqueries? What is the most efficient way to exclude the duplicates?

Upvotes: 2

Views: 51

Answers (2)

GoldenJoe
GoldenJoe

Reputation: 8002

After adding a few indexes, I found this query to work efficiently:

SELECT orders.orderID
FROM orders
WHERE orders.orderID NOT IN  
( 
    SELECT votehistory.orderID 
    FROM users 
    JOIN votehistory
    ON (votehistory.userID = users.userID) 
    WHERE users.userID = [userID input value]   
)
AND orders.userID != [userID input value]
AND orders.orderID > 
(
    SELECT lastViewedOrderID
    FROM users
    WHERE userID = [userID input value]
)
ORDER BY orders.orderID ASC

Upvotes: 0

Arion
Arion

Reputation: 31239

Because you just want orderID you could do it with an EXISTS. Like this:

SELECT orders.orderID
FROM orders
WHERE EXISTS 
( 
    SELECT NULL 
    FROM users 
    JOIN votehistory
    ON (votehistory.userID = users.userID) 
    WHERE users.userID != orders.userID 
    AND users.userID = [the userID value passed in] 
    AND (votehistory.orderID != orders.orderID )
    AND orders.orderID > users.lastViewedOrderID
    AND likehistory.orderID != orders.orderID
)

Reference:

Upvotes: 1

Related Questions