CODEWITHSUNDEEP
mysqlsql
bluedevil2k
bluedevil2k

Reputation: 9491

SELECT Statement Across 2 Tables

I have a web app I'm trying to profile and optimize and one of the last things is to fix up this slow running function. I'm not an SQL expert by any means, but know that doing this in a one step SQL query would be far faster than doing it the way I'm doing now, with multiple queries, sorting, and iterating over loops.

The problem is basically this - I want the rows of data from the "users" table, represented by the UserData object, where there are no entries for that user in the "bids" table for a given round. In other words, which bidders in my database haven't submitted a bid yet.

In SQL pseudo-code, this would be

SELECT * FROM users WHERE users.role='BIDDER' AND 
users.user_id CANNOT BE FOUND IN bids.user_id WHERE ROUND=?

(Obviously that's not valid SQL, but I don't know SQL well enough to put it together properly).

Thanks!

Upvotes: 0

Views: 78

Answers (5)

Anon
Anon

Reputation: 10908

Another alternative for performance comparison. Given your pseudocode, you may find it more readable than the left join.

SELECT *
FROM users u
WHERE u.role='BIDDER'
  AND NOT EXISTS (
    SELECT 1
    FROM bids b
    WHERE b.round = ?
      AND b.user_id = u.user_id
  )

Upvotes: 0

Fabian
Fabian

Reputation: 2972

You can do that with a left join (as proposed by Dave) or using a sub-query:

select * 
from users u 
where u.role = 'BIDDER' and not exists (
    select *
    from bids b
    where b.user_id = u.user_id and b.round = ?)

Or equivalently

select * 
from users u 
where u.role = 'BIDDER' and u.user_id not in (
    select b.user_id
    from bids b 
    where b.round = ?)

EDIT

If you want to use the left join and you want to add the query parameter, you have to put it in the ON clause (not the WHERE clause):

SELECT * FROM users u
LEFT JOIN bids b
ON u.user_id = b.user_id AND b.round = ?
WHERE u.role='BIDDER' AND  b.bid_id IS NULL

Upvotes: 0

Mike Gardner
Mike Gardner

Reputation: 6651

SELECT * from users
WHERE users.rol='BIDDER'
AND users.user_id not in (SELECT user_id FROM bids WHERE round =?)

Upvotes: 0

juergen d
juergen d

Reputation: 204766

SELECT u.* 
FROM users u
LEFT OUTER JOIN bids b on b.user_id = u.user_id
WHERE u.role = 'BIDDER' 
AND b.user_id IS NULL

See this great explanation of joins

Upvotes: 0

Dave
Dave

Reputation: 2984

You can do this with a LEFT JOIN. The LEFT JOIN creates a link between two tables, just like the INNER JOIN, but will also includes the records from the LEFT table ( users here ) that have no association with the right table.

Doing this, we can now add a where clause to specify we want only records with no association with the right table.

The right and left tables are determined by the order you write the join. The left table is the first part and the right table (bids here) is on the right part.

SELECT * FROM users u
LEFT JOIN bids b
ON u.user_id = b.user_id
WHERE u.role='BIDDER' AND 
b.bid_id IS NULL

Upvotes: 2

Related Questions