RIGHT join expression not supported?

[ittechguy]

I'm trying to build a query which contains a RIGHT join between tblEquipment and tblCabinets, also tblEquipment and tblSoftware version. it is not working, its telling me join expression not supported.



Basically, I'm trying to display all records in tblEquipment even if they don't have a CabinetFK or SoftwareVersionFK. What am I doing wrong?

SELECT tblbuilding.buildingname,        tblroom.roomname, 
       tblcabinet.cabinetname, 
       tblkeynum.keynumname, 
       tblmodelnum.modelnum, 
       tblequipmenttype.equipmenttype, 
       tblequipmentbrand.brandname, 
       tblequipment.equipmentname, 
       tblequipment.equipmentip, 
       tblsoftwareversion.softwareversion, 
       tblequipment.serialnum 
FROM   (tblequipmenttype 
        INNER JOIN ((tblequipmentbrand 
                     INNER JOIN tblmodelnum 
                             ON tblequipmentbrand.equipmentbrandpk = 
                                tblmodelnum.equipmentbrandfk) 
                    RIGHT JOIN tblsoftwareversion 
                            ON tblequipmentbrand.equipmentbrandpk = 
                               tblsoftwareversion.equipmentbrandfk) 
                ON tblequipmenttype.equipmenttypepk = 
                   tblmodelnum.equipmenttypefk) 
       INNER JOIN ((tblbuilding 
                    INNER JOIN tblroom 
                            ON tblbuilding.buildingpk = tblroom.buildingfk) 
                   INNER JOIN ((tblkeynum 
                                INNER JOIN tblcabinet 
                                        ON tblkeynum.keynumpk = 
                                           tblcabinet.keynumfk) 
                               RIGHT JOIN tblequipment 
                                       ON tblcabinet.cabinetpk = 
                              tblequipment.cabinetfk) 
                           ON tblroom.roompk = tblcabinet.roomfk) 
               ON ( tblsoftwareversion.softwareversionpk = 
                    tblequipment.softwareversionfk ) 
                  AND ( tblmodelnum.tblmodelnumpk = 
                        tblequipment.equipmentmodelnumfk );

[ItsMe]

Why not use the query designer to build your query? I usually start by adding the table that I want all records returned. I add that table first. Then I create left joins on subsequent tables. If you want to create an inner join, afterwards, you are going to have to include it as a subquery and retrieve those records first. The easiest would be to build a separate query object and left join on the query object that has the inner join.

[ittechguy]

I did use the query designer to build the query. And then I've tried manually setting it to RIGHT join (as you see above), as well as right clicking on the relationship and selecting it to display all values from tblEquipment. When I do this, it gives me a warning saying that I'm going to need to use a subquery. I'm not sure what or how that needs to be done.

I've used right joins before. Most this database has them actually, but not for this many fields across so many tables.

[ittechguy]

Marking this as solved.

I did what you said and used the query designer, first starting with the table which I wanted to see everything of, then I added fields one table at a time, testing it before adding another field. It works perfectly!