How to Query multiple tables and see unrelated results

[ittechguy]

Not sure if I have a database design flaw or SQL so sorry if I'm in the wrong section.



I have a union query which combines two queries: One that searches tblCustomer and tblBuilding and displays all information for customers who are a facility manager for a building, and another query which searches tblCustomer and tblRooms and displays all information for customers who are points of contact for a room. The SQL is below.

SELECTtblcustomer.lastname,
tblcustomer.firstname,
tblcustomer.organizationfk,
tblcustomer.shopnamefk,
tblcustomer.officesymfk,
tblfacilitymgr.customerfk,
tblfacilitymgr.buildingfk,
tblrooms.roomspk
FROM(tblbuilding
INNERJOINtblrooms
ONtblbuilding.buildingpk=tblrooms.buildingfk)
INNERJOIN(tblcustomer
INNERJOINtblfacilitymgr
ONtblcustomer.customerpk=
tblfacilitymgr.customerfk)
ONtblbuilding.buildingpk=tblfacilitymgr.buildingfk
UNION
SELECTtblcustomer.lastname,
tblcustomer.firstname,
tblcustomer.organizationfk,
tblcustomer.shopnamefk,
tblcustomer.officesymfk,
tblroomspoc.customerfk,
tblrooms.buildingfk,
tblroomspoc.roomsfk
FROMtblrooms
INNERJOIN(tblcustomer
INNERJOINtblroomspoc
ONtblcustomer.customerpk=tblroomspoc.customerfk)
ONtblrooms.roomspk=tblroomspoc.roomsfk;

Here is my table relationships:


I use the union query as the recordsource for a form and then I filter the form using several search boxes. Organization, Shop Name, Office Symbol, Last Name, First Name, Building Name, and Room Name.

All this works great. However I also need to be able to search by equipment information (such as Brand, Type, Name, Serial Number, IP address, etc). So I expanded a union query to look like this:

 SELECT tblcustomer.organizationfk,      
       tblcustomer.shopnamefk,
       tblcustomer.officesymfk,
       tblcustomer.lastname,
       tblcustomer.firstname,
       tblfacilitymgr.buildingfk,
       tblrooms.roomspk,
       tblbuilding.buildingname,
       tblrooms.roomname,
       tblcabinet.cabinetname,
       tblequipment.cabinetfk,
       tblequipment.equipmentnamefk,
       tblequipment.equipmentbrandfk,
       tblequipment.equipmentnetworktypefk
FROM   ((tblbuilding
         INNER JOIN tblrooms
                 ON tblbuilding.buildingpk = tblrooms.buildingfk)
        INNER JOIN (tblcustomer
                    INNER JOIN tblfacilitymgr
                            ON tblcustomer.customerpk =
                               tblfacilitymgr.customerfk)
                ON tblbuilding.buildingpk = tblfacilitymgr.buildingfk)
       INNER JOIN (tblcabinet
                   INNER JOIN tblequipment
                           ON tblcabinet.cabinetpk = tblequipment.cabinetfk)
               ON tblrooms.roomspk = tblcabinet.roomsfk
UNION
SELECT tblcustomer.organizationfk,
       tblcustomer.shopnamefk,
       tblcustomer.officesymfk,
       tblcustomer.lastname,
       tblcustomer.firstname,
       tblrooms.buildingfk,
       tblrooms.roomspk,
       tblbuilding.buildingname,
       tblrooms.roomname,
       tblcabinet.cabinetname,
       tblequipment.cabinetfk,
       tblequipment.equipmentnamefk,
       tblequipment.equipmentbrandfk,
       tblequipment.equipmentnetworktypefk
FROM   ((tblbuilding
         INNER JOIN tblrooms
                 ON tblbuilding.buildingpk = tblrooms.buildingfk)
        INNER JOIN (tblcabinet
                    INNER JOIN tblequipment
                            ON tblcabinet.cabinetpk = tblequipment.cabinetfk)
                ON tblrooms.roomspk = tblcabinet.roomsfk)
       INNER JOIN (tblcustomer
                   INNER JOIN tblroomspoc
                           ON tblcustomer.customerpk = tblroomspoc.customerfk)
               ON tblrooms.roomspk = tblroomspoc.roomsfk;

This shows the following results:


The issue I'm having is that the only way this will show a customer and the buildings/rooms they're assigned to is if I have a value in tblEquipment.CabinetFK. This cannot work because not all rooms have equipment. I need to be able to search for a name, building, room, etc and see records even if there is no equipment assigned to that room. I also need to be able to search for equipment that IS assigned to a room (or more accurately, a cabinet which is assigned to a room).

As it is now, there are many rooms in my example database which have customers assigned to them, but they do not have any cabinets in them (yet) and thus they do not have any equipment in them. My first query would display all information, but my second only displays information if the equipment is assigned to a room. I need to be able to see all records for my search whether there is a cabinet in a room or not, and whether there is equipment in a cabinet or not. BUT, I also need to be able to search by equipment which is in a cabinet.

I hope I've been clear enough on what I need to do. What can I do to fix this?

[ittechguy]

Is it possible perhaps I just need to change my query? Perhaps using a left/right join instead of inner join? I do not yet know enough about SQL to know how to do this, but I'm doing all the research I can do.

[ittechguy]

Figured it out! All I had to do was change the join from tblCabinets to tblEquipment from INNER JOIN to LEFT JOIN.

SELECT tblcustomer.organizationfk,             tblcustomer.shopnamefk,
       tblcustomer.officesymfk,
       tblcustomer.lastname,
       tblcustomer.firstname,
       tblfacilitymgr.buildingfk,
       tblrooms.roomspk,
       tblbuilding.buildingname,
       tblrooms.roomname,
       tblcabinet.cabinetname,
       tblequipment.cabinetfk,
       tblequipment.equipmentnamefk,
       tblequipment.equipmentbrandfk,
       tblequipment.equipmentnetworktypefk
FROM   ((tblbuilding
         INNER JOIN tblrooms
                 ON tblbuilding.buildingpk = tblrooms.buildingfk)
        INNER JOIN (tblcustomer
                    INNER JOIN tblfacilitymgr
                            ON tblcustomer.customerpk =
                               tblfacilitymgr.customerfk)
                ON tblbuilding.buildingpk = tblfacilitymgr.buildingfk)
       INNER JOIN (tblcabinet
                   INNER JOIN tblequipment
                           ON tblcabinet.cabinetpk = tblequipment.cabinetfk)
               ON tblrooms.roomspk = tblcabinet.roomsfk
UNION
SELECT tblcustomer.organizationfk,
       tblcustomer.shopnamefk,
       tblcustomer.officesymfk,
       tblcustomer.lastname,
       tblcustomer.firstname,
       tblrooms.buildingfk,
       tblrooms.roomspk,
       tblbuilding.buildingname,
       tblrooms.roomname,
       tblcabinet.cabinetname,
       tblequipment.cabinetfk,
       tblequipment.equipmentnamefk,
       tblequipment.equipmentbrandfk,
       tblequipment.equipmentnetworktypefk
FROM   ((tblbuilding
         INNER JOIN tblrooms
                 ON tblbuilding.buildingpk = tblrooms.buildingfk)
        LEFT JOIN (tblcabinet
                    LEFT JOIN tblequipment
                            ON tblcabinet.cabinetpk = tblequipment.cabinetfk)
                ON tblrooms.roomspk = tblcabinet.roomsfk)
       INNER JOIN (tblcustomer
                   INNER JOIN tblroomspoc
                           ON tblcustomer.customerpk = tblroomspoc.customerfk)                ON tblrooms.roomspk = tblroomspoc.roomsfk;