Postgresql – Based on the result from the table, get the result from another table

postgresql

I have two tables users and products:

1) users: 

 id |  username    | ... |
--------------------------
 1  | user1name 1  | ... |
 2  | user2name 2  | ... |
 3  | user3name 4  | ... |
 4  | user4name 5  | ... |

2) products:

id |  product_name   | user_id |
--------------------------------
 1 |  product1name 1 |    1    |
 2 |  product2name 2 |    1    |
 3 |  product3name 3 |    2    |
 4 |  product4name 4 |    3    |

I want to get products based on the result from the users table. I have a user table connection with a product table.

My request:

SELECT P.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.USER_ID)
            WHERE LOWER(U.USERNAME) LIKE LOWER('%use1r%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID

What answer I expect to receive:

id |  product_name   | user_id |
--------------------------------
 1 |  product1name 1 |    1    |
 2 |  product2name 2 |    1    |

Best Answer

You need to move the condition on the username into the JOIN condition. Applying a WHERE condition on the outer joined table turns your outer join back into an inner join.

Additionally - given your sample data - %use1r% won't match any row, I assume you meant to write %user1%. As you query only returns columns from the products table, I think you meant to use p.user_id in the group by as ell to remove duplicate products that stem from the join. 

SELECT p.*
FROM products AS p
  LEFT JOIN users AS U ON u.user_id = p.user_id
                      AND LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

But given your expected output, it seems you want an inner join as you only want products assigned to the matching user.

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
                 AND LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

With an inner join it doesn't matter where you put the condition on the username. The above is equivalent to:

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
WHERE LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

But given your usage of the group by I wonder if you are trying to remove duplicate products from the result. In that case an EXISTS query is maybe what you really want

SELECT p.*
FROM products AS p
WHERE EXISTS (SELECT * 
              FROM users u
              WHERE u.user_id = p.user_id
                AND LOWER(u.username) LIKE LOWER('%use1r%'));

Then you don't need to remove unwanted duplicates with a group by

Related Question