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

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.

Answer

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

Advertisement

Answer