CODEWITHSUNDEEP
mysql
puks1978
puks1978

Reputation: 3697

MySQL Select multiple distinct

I have one table which holds lots of records. Its for an auction site. This table can have multiple user_id which can be the same and multiple auction_id which can be the same.

I am trying to write a script that sends an email once to each user that has placed a bid. If a user places say 10 bids on the same auction, I only want the email to be sent once per user per auction, per bid.

How would I do this with distinct over 2 fields? The user_id field and the auction_listing field? I will also need to have a WHERE clause so I only select records of auctions that have less than 24 hours to run.

Upvotes: 0

Views: 390

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562691

SELECT DISTINCT b.user_id, b.auction_listing
FROM AuctionBids b
JOIN Auctions a USING (auction_listing)
WHERE a.end_datetime < NOW() + INTERVAL 24 hour

Or

SELECT b.user_id, b.auction_listing
FROM AuctionBids b
JOIN Auctions a USING (auction_listing)
WHERE a.end_datetime < NOW() + INTERVAL 24 hour
GROUP BY b.user_id, b.auction_listing

Upvotes: 1

parapura rajkumar
parapura rajkumar

Reputation: 24413

Without knowing your table schema it is hard to answer to answer you question.

In general if you do a GROUP_BY user_id in the end one user will only appear once in your query results

Upvotes: 0

Related Questions