INNER and LEFT JOIN on mysql

Question

I have the needing to chain some joins queries, and I am not able to get the result that I want.
I have many tables to save some product info:

-Products: Name and description in many languages.

-Products_attributes: Some common attributes of the products like the provider or the buying price.

-Products_locations: Info concerning the locations that sell a certain product, like stock, or the selling price.

-Other important table is the Companies one: some of this companies could be a provider.

Well, this is my query:

SELECT p.id
     , p.name nameProduct
     , p.reference refProduct
     , a.buy_price priceProduct
     , l.active
     , l.sell_price
     , l.stock stockProduct
     , c.title p_name
  FROM products p
  LEFT JOIN products_location l
    ON l.product_reference = p.reference 
   AND l.active = 1 
   AND l.location_id = 4 
  JOIN products_attributes a
    ON a.product_reference = p.reference 
   AND p.lang = 'es' 
   AND a.provider = 6
  JOIN companies c
    ON a.provider = c.id 
   AND c.id = 6

What I want to do is get all products of a certain provider, if the location from where is executing the query has this product, the row of the result must return too the concerning at this product<->location, in contrary it must return NULL in the column related to this relation.
At the moment, with this query, all what I am getting is the products of a provider where has a relation between the product<->location (through products_location table).

Any way to do that??

Thank you.

EDIT

An example about what I trying to get could be something like this:

TABLE: Companies

id | Title 
1  | SomeName
6  | ProviderName

TABLE: Products

id | reference | name | lang
1  | 11111     | 1_es | es
2  | 11111     | 1_en | en
3  | 22222     | 2nam | es
4  | 33333     | 3nam | es
5  | 44444     | 4nam | es
6  | 55555     | 5nam | es

TABLE: Products_atributte

id | product_reference | buy_price | provider
1  | 11111             | 10        | 6
1  | 22222             | 15        | 6
1  | 33333             | 20        | 6
1  | 44444             | 12        | 1
1  | 55555             | 13        | 1

TABLE: Products_locations

id | product_reference | location_id | sell_price | stock | active
1  | 11111             | 4           | 26          | 10    | 1
1  | 11111             | 5           | 25          | 13    | 1
1  | 22222             | 5           | 20          | 13    | 1
1  | 44444             | 5           | 21          |  1    | 1
1  | 55555             | 5           | 22          |  2    | 1

AND THE RESULT MUST BE SOMETHING LIKE THIS:

nameProduct | refProduct | priceProduct | active | sell_price | stockProduct | p_name
1_es        | 11111      | 10           | 1      | 26         | 10           | ProviderName
2nam        | 22222      | 15           | NULL   | NULL       | NULL         | ProviderName
3nam        | 33333      | 20           | NULL   | NULL       | NULL         | ProviderName

If I use a LEFT JOIN only in the products_locations table, I don´t get the two last rows, and if I use LEFT JOIN with all the tables I get duplicates product references, also I get products provided by other providers (in the example 1-> SomeName).

Answer 1

You were correct to LEFT JOIN the products and products_location tables. However, you used INNER JOIN for the other two tables in the query and I believe that this may be the reason why you are only seeing records which have a relation between product and location. The logic would be that a product which does not have a location also does not have an entry in, for example, the products_attributes table. Hence, the non matching records you want to retain would be filtered off downstream by an INNER JOIN. To remedy this, use LEFT JOIN everywhere:

SELECT products.id,
       products.name AS nameProduct,
       products.reference AS refProduct,
       products_attributesbuy_price AS priceProduct,
       products_location.active,
       products_location.sell_price,
       products_location.stock AS stockProduct,
       provider.title AS p_name
FROM products
LEFT JOIN products_location
    ON products_location.product_reference = products.reference AND
       products_location.active = 1 AND
       products_location.location_id = 4
LEFT JOIN products_attributes
    ON products_attributes.product_reference = products.reference AND
       products.lang = 'es' AND
       products_attributes.provider = 6
LEFT JOIN companies AS provider
    ON products_attributes.provider = provider.id AND
       provider.id = 6