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.
>Solution :
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