perkes456 perkes456 - 1 year ago 52
SQL Question

Getting corresponding and non corresponding values in many to many table

I have a table structure in my sql like following:

Brand -> BrandUser <- Users

The BrandUser table contains userId and brandId columns. I need to get ALL brands for which are registered, and for which they are not registered.

So for instance lets say I have userId = 2 which is repeating itself twice in brandUser table and I send the userId = 2... I need to get those TWO records, as well as the rest of the brands from Brands table for which user DOES NOT HAVE VALUE in BRANDUSER table. I have written the following query:

$col = ",

$sql = " SELECT $col
FROM brands b
LEFT JOIN branduser bu on bu.brandId =
LEFT JOIN users u on bu.userId =
WHERE bu.userId = $userId";

$brandListUserRegistered = $this->getBrandModel()->getBrandsForUser($sql);

And this is the getBrandsForUser action which executes the query:

public function getBrandsForUser($sql){
$resultSet = $this->dbAdapter->query($sql,Adapter::QUERY_MODE_EXECUTE);
return $resultSet->toArray();

The main thing I'm focusing on right now is the query itself... with the query above I only get brand records for the user that actually HAS corresponding records in branduser table, and thats not what I want... :/

Answer Source

You could just use BrandUser as your base table and then left join to the other tables;

FROM BrandUser bu
    ON bu.brandId =
LEFT JOIN users u
    ON bu.userId =
WHERE bu.userId = $userId

This way you will just return NULL values if there is not corresponding data in the Brand table.

My apologies, I misread your question, try changing the WHERE clause so that it is included in the join;

FROM Brand b
LEFT JOIN BrandUser bu
    ON bu.brandId =
    AND bu.userId = $userId
LEFT JOIN users u
    ON bu.userId =