I have 2 table with the following data
ManualVersions - list of all manual versions
+------------------+---------------+--------------+
|Id | ManualID | VersionNumber | VersionName |
+-----+------------+---------------+--------------+
|10 | 12 | 1.0 | Version 1 |
|17 | 12 | 2.0 | Version 2 |
CustomersManuals - This lets me know which customer have access to which manual versions
+---+---------------+--------------------+
|Id | CustomerID | ManualVersionsID |
+-- +---------------+--------------------+
|4 | 19 | 10 |
|8 | 24 | 10 |
The goal is to write a query that lets me know which manual versions a customer does and does not have access to.
To do this I run the following sql on the above tables.
Select CustomersManuals.id as CustomerManualsID, ManualVersions.VersionNumber, ManualVersions.VersionName, ManualVersions.id as ManualVersionID
FROM CustomersManuals
RIGHT OUTER JOIN ManualVersions ON CustomersManuals.ManualVersionsID = ManualVersions.id
WHERE (CustomersManuals.CustomerID=24 OR CustomersManuals.CustomerID iS NULL) AND ManualVersions.ManualID = 12
Results from above SQL
+-------------------+--------------+---------------+----------------+
|CustomerManualsID | VersionNumber| VersionName | ManualVersionID|
+-------------------+--------------+---------------+----------------+
| 8 | 1.0 | Version 1 | 10 |
| NULL | 2.0 | Version 2 | 17 |
The above is correct and does what I want. Null is returned showing customerID 24 does not have manual 2.
If I now INSERT the following row to the CustomersManuals table the above SQL will not work
CustomersManuals (row i inserted)
+----+------------+------------------+
| Id | CustomerID | ManualVersionsID |
+----+------------+------------------+
|30 | 18 | 17 |
The above sql now only returns 1 row
+-------------------+---------------+---------------+---------------------+
| CustomerManualsID | VersionNumber | VersionName | ManualVersionID |
+-------------------+---------------+---------------+---------------------+
| 8 | 1.0 | Version 1 | 10 |
Note in the above table the Row that had null against the CustomerManualsID has gone.
Is there a way to show all the manuals a customer has and all the ones they don’t have?
Put the conditions on the child table in the ON clause, not WHERE. Then you don't need to test explicitly for NULL.
Select CustomersManuals.id as CustomerManualsID, ManualVersions.VersionNumber, ManualVersions.VersionName, ManualVersions.id as ManualVersionID
FROM CustomersManuals
RIGHT OUTER JOIN ManualVersions ON CustomersManuals.ManualVersionsID = ManualVersions.id AND CustomersManuals.CustomerID=24
WHERE ManualVersions.ManualID = 12