Struggling with left join - products

Question

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 |
+-----------+----------+-------+

Answer 1

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'.

Answer 2

If products are assigned to clients exclusively only, i.e. if the same product cannot be assigned to two or more clients at the same time, you can also use this query, which, in a way, is a variation on seventyeightist's solution:

SELECT
  ...
FROM
  product AS p
  LEFT JOIN product_client AS pc ON p.ID = pc.product_ID AND pc.client_ID <> 1
WHERE
  pc.ID IS NULL
;

The query is trying to match each product with an entry in product_client that is associated with a client other than client_ID=1. If there is no match (WHERE pc.ID IS NULL), such a product should be in the output, because that means either

• the product is global (it is not in the table to begin with, thus failing the p.ID = pc.product_ID part of the joining condition),

  or

• the product is associated with the client we want (and, therefore, fails the other part, pc.client_ID <> 1).