Need help in getting results from three different tables

Question

I have these three tables:

products:

+----+--------------+
| id | product_name | 
+----+--------------+
| 12 | a            |    
| 13 | b            |
| 14 | c            |
| 15 | d            | 
| 16 | e            |
| 17 | f            |
| 18 | g            |
| 19 | h            | 
| 20 | i            |
| 21 | j            |
+----+--------------+

connections:

+----+------------+----------------------+
| id | product_id | connected_product_id |
+----+------------+----------------------+
| 1  | 21         | 18                   | 
| 2  | 21         | 19                   | 
| 3  | 21         | 20                   | 
+----+------------+----------------------+

proposals:

+----+-------------+-------------+
| id | proposer_id | receiver_id |
+----+-------------+-------------+
| 2  | 21          | 16          | 
| 3  | 21          | 17          | 
| 4  | 21          | 18          | 
+----+-------------+-------------+

And I would like, for some product X, to get all the products, that are not connected to X, and also there are no proposals between them (X and those products, are not proposers neither receivers to each other).

I am trying this humble statement:

SELECT * 
FROM products 
WHERE id != X_id 
AND id NOT IN (
        SELECT connected_product_id 
        FROM connections 
        WHERE product_id = X_id
)

But that gets me all the products that are not connected to X.

I need that final step where I want to do something like this:

SELECT * 
FROM products 
WHERE id != X_id
 AND id NOT IN ( 
        SELECT product_id
        FROM proposals
        WHERE product_id != X_id
        AND receiver_id NOT IN (
            SELECT connected_product_id 
            FROM connections 
            WHERE product_id = X_id
        )
)

But for the above, and let's say product X is id=21, the current output is:

All Products Other Than 21

And I expected output to be:

star from products where id in 12, 13, 14, 15 (which are not connected to X=21, and there are no proposals between them and X=21)

 +----+--------------+
 | id | product_name | 
 +----+--------------+
 | 12 | a            |    
 | 13 | b            |
 | 14 | c            |
 | 15 | d            | 
 +----+--------------+

I use javascript so the result would be an array of objects {id, product_name}

Any help?

Answer 1

You had it mostly correct, but had the following two mistakes:

1. You used product_id when you should have used proposer_id
2. You also had a != inside of a not in which creates a double negative.

The correct statement should look something like this:

select * from products where
    id not in (select connected_product_id from connections where product_id = 21)
    and id not in (select receiver_id from proposals where proposer_id = 21)
    and id != 21;

Answer 2

Try this:

SELECT *
FROM products 
WHERE id != 21
 AND id NOT IN ( 
        SELECT connected_product_id 
            FROM connections 
            WHERE product_id = 21
        UNION ALL
        SELECT receiver_id
        FROM proposals
        WHERE proposer_id =21
)

demo

| id | product_name |
|----|--------------|
| 12 |            a |
| 13 |            b |
| 14 |            c |
| 15 |            d |