Left JOIN problem..I cannot see (1 Viewer)

[tt1611]

Very simple


SELECT PCS.Device_Name, PCS.Model, PCS.Manufacturer, PCS.Serial, PCS.Reg_User, D.Device_Model, D.Device_Serial, D.Device_Type
FROM PCS LEFT JOIN devices AS D ON PCS.Device_Name = D.PC_Name
WHERE (((PCS.Device_Name) Like 'ARTI%' Or (PCS.Device_Name) Like 'HOHE%' Or (PCS.Device_Name) Like 'GRAF%' Or (PCS.Device_Name) Like 'VILS%') AND ((D.Device_Type)="Monitors"))
ORDER BY PCS.Device_Name;

The above join statement is SUPPOSED to show me all PCs AND related devices from the device table where device_type is a monitor.
Again show all PCs and matched monitors if exists one
else show the PC and null value for monitors

That is the general idea. However the query is acting like an INNER JOIN and only showing matched records from both tables where device type is a monitor which leaves me about 200 records short.

I have tested this for LEFT, INNER and RIGHT. Right shows me all monitors like it is supposed to but LEFT and INNER are showing the exact same records. Can you see what I may be doing wrong here. Last I checked, LEFT JOINs were supposed to show all records from LEFT table and matched records from RIGHT.

 

[Galaxiom]

AND (D.Device_Type="Monitors" OR D.Device_Type Is Null)

 

[tt1611]

Galaxiom
That worked like a charm. My question now is shouldn't the LEFT JOIN clause take care of returning null values if it cannot find a matching monitor?

 

[Galaxiom]

Yes, but that would be human logic. This is a computer and even though it did say "all records from".....

But no, because the Where clause applies to all records returned by the join.

If it is any consolation I would guess that this particular curiosity has left many a new developer scratching their head for a while.

 

[tt1611]

Lol...I've been scratching mine for 2 hours..

Thanks for your help men..I'll know the route to take when this occurs.