Need help joining three tables - TOO complex.

[robnyc]

Hello,
I am new to the forum and cannot figure this complex join..

I have three tables..
1. ObjectDesc
- objId
- objName
2. ServerInfo
- ServerId
-ServerStatus
3. DriveDesc
- DriveServerId
- DriveId
- DriveSize

Here is what I need..
I need to join table3 with table 1 using DriveId=objId, join table2 with table1 using ServerId=objId & join table2 with table3 using ServerId=DriverServerId

I want the query to give me the following
ServerName(objName), ServerStatus, DriveName(objName), DriveSize

The names of the Server and Drive are in the ObjectDesc table under the objName field.

thank you very much.

[SDas]

Robnyc, there may be other ways to do this but this one is fairly easy to follow. It attacks the problem by breaking it down into smaller problems.

First join the ObjectDesc table with the ServerInfo table to get Server information.
Next join the ObjectDesc table with the DriveDesc table to get Drive information.
Finally, using the two queries above as nested tables, join the Server info and the Drive info nested tables together.

1) Server info

SELECT TAB2.SERVERID
, TAB2.SERVERSTATUS
, TAB1. OBJNAME
FROM OBJECTDESC TAB1
, SERVERINFO TAB2
WHERE TAB1.OBJID = TAB2.SERVERID

2) Drive info

SELECT TAB3.DRIVESERVERID
, TAB3.DRIVESIZE
, TAB1.OBJNAME
FROM OBJECTDESC TAB1
, DRIVEDESC TAB3
WHERE TAB1.OBJID = TAB3.DRIVEID

3) Join the Server and Drive info 'tables' together.

SELECT SRV.OBJNAME
, SRV.SERVERSTATUS
, DRV.OBJNAME
, DRV.DRIVESIZE
FROM
(
SELECT TAB2.SERVERID
, TAB2.SERVERSTATUS
, TAB1. OBJNAME
FROM OBJECTDESC TAB1
, SERVERINFO TAB2
WHERE TAB1.OBJID = TAB2.SERVERID
) AS SRV
,
)
SELECT TAB3.DRIVESERVERID
, TAB3.DRIVESIZE
, TAB1.OBJNAME
FROM OBJECTDESC TAB1
, DRIVEDESC TAB3
WHERE TAB1.OBJID = TAB3.DRIVEID
) AS DRV
WHERE SRV.SERVERID = DRV.DRIVESERVERID

[MikeH]

Robnyc,
Maybe the following if it works on your RDBMS. Syntax may not be perfect.

SELECT SOD.OBJNAME ,
SRV.SERVERSTATUS ,
DOD.OBJNAME ,
DRV.DRIVESIZE
FROM SERVERINFO SRV,
INNER JOIN
DRIVEDESC DRV,
ON SRV.SERVERID = DRV.DRIVESERVERID
INNER JOIN
OBJECTDESC SOD,
ON SOD.OBJID = SRV.SERVERID
INNER JOIN
OBJECTDESC DOD,
ON DOD.OBJID = DRV.DRIVEID