Home > Net >  SQL query to fetch all Auctions for whom a particular user has not submitted a Bid
SQL query to fetch all Auctions for whom a particular user has not submitted a Bid

Time:07-15

I am working on a project where I want to fetch from my postgres database all the auctions where a particular user has not submitted any bid for the auctions. I am trying to write the query for this. The schema is as follows -

Auction Table

auction_id (PK) auction_data
A1 a1_data
A2 a2_data
A3 a3_data

Bids Table

bid_id (PK) auction_id (FK) user_id bid_price
B1 A1 U1 P1
B2 A2 U1 P2
B3 A1 U2 P3
B4 A2 U2 P4
B5 A3 U2 P5

The constraint is that for each auction any user can submit only a single bid. So ideally the query should return Auction id as A3 if the input to it is find auctions where user U1 has not submitted a bid.

How to accomplish this? I know we can do multiple joins to find the result but I am looking for something a little straight forward. Not sure if there is any in-built function to make the query a little less complex. I don't have much experience so struggling to come up with a solution.

CodePudding user response:

SELECT A.AUCTION_ID
   FROM AUCTION_TABLE A
EXCEPT
SELECT B.AUCTION_ID
  FROM BIDS_TABLE B WHERE B.USER_ID='U1'

Could you please try this

  • Related