How to Join tables only where table1.column2 - table2.column3 > 0 (or !=0) Postgresql

EyeDunno
March 19, 2024
187 views
0 votes
2 Answers

I’m still a novice to sql, using Postgres. i have two tables:

table request

order_id	amount_requested
51	5
25	8
12	1

table response

order_id	amount_added
51	5
25	4
12	0

I am trying to get only the order_id’s where the response.amount_added is less than the request.amount_requested, and add a column with the difference. So in this case the result would be:

order_id	amount_requested	amount_added	amount_remaining
25	8	4	4
12	1	0	1

order_id 51 wasn’t in the result (desired outcome), because amount requested(5) was not less than the amount_added.

The query I am trying does not return any rows, even when the amount_request > amount_added

I tried-


    SELECT request.order_id, 
           request.amount_requested, 
           response.amount_inserted, 
           request.amount_requested - response.amount_added as amount_remaining 
    FROM request 
    INNER JOIN response
    ON request.order_id = response.order_id
    WHERE request.amount_requested > response.amount_added




**This query output no rows.**

However I know it’s mostly working, because if I remove

    WHERE request.to_disp > response.amount_added

It does show the results! HOWEVER, it includes the rows that don’t have a remainder:

order_id	amount_requested	amount_added	amount_remaining
51	5	5	0 — Don’t want this row!
25	8	4	4
12	1	0	1

So, I'd like to only include rows that either :

have amount_remaining != 0
request.amount_requested > response.amount_added
request.amount_requested - response.amount_added> 0
request.amount_requested - response.amount_added!= 0

Any of those conditions should work, as the amount_added will never be larger than the amount requested

I am not sure why the WHERE condition is not working properly. Thank you for any help!

Tags: postgresql sql

Answers

- AlexanderPetrov
- March 19, 2024 at 9:12 pm
- 0 votes
0
You can use a derived table:
```
select * from (
    select t1.order_id,(t1.amount_requested - t2.amount_added) as amount_remaining 
    from request t1 join response t2 on t1.order_id=t2.order_id ) as d where d.amount_remaining > 0
```
Here is the test 🙂
https://www.db-fiddle.com/f/hR2h8BUPefa2JrBZX3etVp/0
Login or Signup to reply.

- Ianis
- March 19, 2024 at 10:44 pm
- 0 votes
0
The above answer is flat out wrong, you can obtain the wanted result using a query like this
```
SELECT *, amount_requested - amount_added AS remaining 
FROM response NATURAL JOIN request
WHERE amount_requested > amount_added
```
To explain this in more detail since OP is rather new.

The query will run these operations in the following order,
1. It will compute the to be processed table in the FROM clause
2. It will weed out any values that violate the WHERE clause
3. It will compute the SELECT clause
FROM: The result of response NATURAL JOIN request is equivalent to doing a CROSS JOIN (Cartesian Product) and then selecting only the tuples where the attributes with matching names are equal, to put this into SQL terms, this query will have an equivalent result
```
SELECT * 
FROM response, request
WHERE response.order_id = request.order_id
```
With one difference from the above query will have 4 attributes response.order_id, amount_added, request.order_id, amount_requested While NATURAL JOIN will merge response.order_id with request.order_id.

WHERE: In our query tuples will just remove any rows where amount_requested > amount_added as might be expected aka where amount_requested – amount_added > 0

SELECT: Only now will select values be computed, at this point our working table will look like this

order_id amount_added amount_requested

12 0 1

25 4 8

We can then just select all the preexisting attributes with the * Wild card, and add another attributes that is the remaining amount to be paid (I’m assuming)

Here is a database fiddle that showcases the query and that you can play with, and here is another one with a INNER JOIN since you said you’d like to avoid natural joins
Login or Signup to reply.