Very Computer

I have three tables that are linked together and I'm having difficulty
designing a SQL query to get the correct information.  The setup is this:

tblRollout
-----------
StoreNo (PK)
PCStatus
KitStatus
InstallStatus

tblShipStatus
-------------
ID_tblShipStatus (PK)
StatusText

tblInstallStatus
---------------
ID_tblInstallStatus (PK)
StatusText

Both the tblRollout.PCStatus and tblRollout.KitStatus fields are "lookup"
fields that reference the ID_tblShipStatus from tblShipStatus.

I can create a SQL Statement "SELECT StoreNo, StatusText FROM tblRollout
INNER JOIN tblShipStatus ON tblRollout.PCStatus =
tblShipStatus.ID_tblShipStatus" which will return the Store Number and the
"text" PC Status.  I'd like to be able to create a SQL Statement that would
return the Store Number, the "text" PC Status, the "text" Kit Status, and
the "text" Install Status from the third table.

I'm relatively new at SQL and appreciate any help you can give me!

Thanks-

Greg

Quote:> I'd like to be able to create a SQL Statement that would
> return the Store Number, the "text" PC Status, the "text" Kit Status, and
> the "text" Install Status from the third table.

You're on the right track. In SQL you can have several joins
in one query. You have the inner join to tblShipStatus. All
you need is to add the inner joins to the other tables you
want data from, and add their "text" columns to your
SELECT clause.

example:

SELECT r.StoreNo,
    ss.StatusText as Ship_Status,
    is.StatusText as Install_status
FROM tblRollout r
    INNER JOIN tblShipStatus ss ON r.PcStatus = ss.ID_tblShipStatus
    INNER JOIN tblInstallStatus is ON r.InstallStatus =
is.ID_tblInstallStatus

Tim

Right.  I can get the PC Ship status and the Install status in one query.
What I'm struggling with is how to get the PC Ship status and the Kit Ship
status when they both reference the same table (tblShipStatus).

Quote:> Right.  I can get the PC Ship status and the Install status in one query.
> What I'm struggling with is how to get the PC Ship status and the Kit Ship
> status when they both reference the same table (tblShipStatus).

Ah, I see -- my apologies for not understanding correctly before.

Join to the tblShipStatus twice using a different alias each time.

SELECT r.StoreNo,
    ss.StatusText as Ship_Status,
    is.StatusText as Install_status,
    ks.StatusText as Kit_status
FROM tblRollout r
    INNER JOIN tblShipStatus ss
        ON r.PcStatus = ss.ID_tblShipStatus
    INNER  JOIN tblShipStatus ks
        ON r.KitStatus = ks.ID_tblShipstatus
   INNER JOIN tblInstallStatus is
        ON r.InstallStatus = is.ID_tblInstallStatus

Tim

Thanks!  That worked perfectly.