How Could I Rewrite This Query to Include Null Results

[Student204]

I have the following two tables that I am using for inventory tracking purposes:

Computers	Monitors
-InventoryNumber [Primary Key] ... -Monitor1 -Monitor2	-InventoryNumber [Primary Key] -SerialNumber -Model -Location

Each computer in the inventory is connected to one or two monitors.

In the Computers Table:
-Monitor1 is a field that contains the InventoryNumber of a particular monitor.
-Monitor2 is either the InventoryNumber of a 2nd (dual) monitor, or null if the computer only has one monitor.

I have the following query:

SELECT Computers.InventoryNumber, Computers.Monitor1, Computers.Monitor2, M1.*, M2.*  
FROM Computers, Monitors as M1, Monitors as M2
WHERE (M1.InventoryNumber = Monitor1 AND M2.InventoryNumber = Monitor2)

Results:

Computers.InventoryNumber	Computers.Monitor1	Computers.Monitor2	M1.InventoryNumber	...	M2.InventoryNumber	...
123456789	123	321	123	...	321	...
234567890	456	654	456	...	654	...
345678901	789	987	789	...	987	...

This displays all of the dual monitor computers correctly in a table with all of the related monitor information. However I would Like to be able to display all of the information for the computers with single monitors and dual monitors in a single table, instead of just the dual monitor computers.

I tried to modify the query as follows:

SELECT Computers.InventoryNumber, Computers.Monitor1, Computers.Monitor2, M1.*, M2.*  
FROM Computers, Monitors as M1, Monitors as M2
WHERE (M1.InventoryNumber = Monitor1 AND M2.InventoryNumber = Monitor2) OR (M1.InventoryNumber = Monitor1 AND Monitor2 IS NULL)

However instead of showing the results for each Computer Inventory Number I am getting repeat listings.

Computers.InventoryNumber	Computers.Monitor1	Computers.Monitor2	M1.InventoryNumber	...	M2.InventoryNumber	...
123456789	123		123	...	124	...
123456789	123		123	...	125	...
123456789	123		123	...	126	...
...	...	...	...	...	...	...

It appears as though each of the computers without a second monitor get listed X times where X is the number of monitors in the database. I'm fairly new to SQL and would love any input as to how I could list both the dual monitor and the single monitor machines in one table.

Thanks,

[Dunro]

I would use a left join...

SELECT Computers.InventoryNumber, Computers.Monitor1, Computers.Monitor2, M1.*, M2.*  FROM (Computers 
 LEFT JOIN Monitors as M1 on Computers.Monitor1 = M1.InventoryNumber) 
 LEFT JOIN Monitors as M2 on Computers.Monitor2 = M2.InventoryNumber

[Student204]

Thanks, this worked.

Out of curiosity how does the LEFT JOIN handle nulls in the comparisons?

[Dunro]

Here's a quick tutorial that illustrates LEFT JOIN better than I can. Basically, everything in the 1st table is guaranteed to appear, and anything in the 2nd (or 3rd) table whose keys match will appear. Keep in mind this can theoretically result in multiple results if the keys are not unique.

http://www.gcflearnfree.org/access2010/8.6