MySQL Left Join – Struggling with Products

MySQL

I got a quite easy question for you.

I have a table

 products [ID,Name,Category,Price]

and a table

product_client[ID,product_ID,client_ID]

The products table contains all the products of course, but what the product_client does is, it is storing some products that are exclusively for a certain client.

So I wrote a small query for this one

SELECT * 
FROM product
LEFT JOIN product_client ON product.ID = product_client.product_id
WHERE product_client.client_id =1

Everything works fine, returns me the products exclusively for this client. But what I want is more – the products table is like a dictionary where on top of that client has his own products. So I need to get the whole products table, plus the query output above.

Any tips? Is it a good idea to have this set up this way?

Consider a case where:

  • In the database we have ids 1.2.3.4.5
  • Product ids 1.2 are assigned to client 1
  • The product ids 3.5 are assigned to client 2.

We want to query for client 1. So we expect to get the following ids: 1.2.5, since 3 belong to clients 2, and number 4 is a global product (unassigned). Any unassigned is a global product.

Products Table

+-----+-------------+-----------+-------+
| ID  |    Name     | Category  | Price |
+-----+-------------+-----------+-------+
|  1  | Paper       | Material  |    10 |
|  2  | Wool        | Material  |     4 |
|  3  | Cleaning    | Service   |    10 |
|  4  | Something   | Service   |     1 |
|  5  | Something2  | Service   |     2 |
+-----+-------------+-----------+-------+

Client Table

+-----+---------+-------------------+
| ID  |  Name   | {anyextra fields} |
+-----+---------+-------------------+
|  1  | Client1 |                   |
|  2  | Client2 |                   |
+-----+---------+-------------------+

product_client Table

+-----+-----------------+-----------+
| ID  | product_client  | client_ID |
+-----+-----------------+-----------+
| 1   |              1  |         1 |
| 2   |              2  |         1 |
| 3   |              3  |         2 |
| 4   |              5  |         1 |
+-----+-----------------+-----------+

Desired output for client_ID = 1

      Query return table
+-----------+----------+-------+
|  Product  | Category | Price |
+-----------+----------+-------+
| Paper     | Material |    10 |
| Something | Service  |     1 |
| Wool      | Material |     4 |
+-----------+----------+-------+

Best Answer

Your "where" clause restricts it to products where it is matching the client products (As you may know, left join returns everything from the left side, with matching entries from the right side or NULL otherwise. But then you filter it for a specific client value) - I would amend your WHERE clause to:

WHERE 
    product_client.client_id = 1 
    OR product_client.client_id IS NULL

My logic is - you are matching either the specific client, or the values that are "unmatched" to another (e.g. client_id = 2) client. client_id is null where the product doesn't match a specific client.

To determine which one is a global product and which one is a client's only, add to the select statement:

CASE 
    WHEN product_client.client_id IS NULL 
        THEN 'Global' 
    ELSE 'Client' 
END

For MySQL/PHP add ( ) around the statement.

You could also ISNULL(client_id,"Global") if wanting to take the specific client if it's specified, or otherwise 'global'.

Related Question