perkes456 perkes456 - 5 months ago 13
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 = "
u.id,
u.username,
u.firstName,
bu.active,
bu.userId,
b.name";

$sql = " SELECT $col
FROM brands b
LEFT JOIN branduser bu on bu.brandId = b.id
LEFT JOIN users u on bu.userId = u.id
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

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

SELECT
$col
FROM BrandUser bu
LEFT JOIN Brand b
    ON bu.brandId = b.id
LEFT JOIN users u
    ON bu.userId = u.id
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;

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