Multiple Lookups to the Same Lookup Table

Multiple Lookups to the Same Lookup Table

Post by Fisbec » Thu, 17 May 2001 09:14:16



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

 
 
 

Multiple Lookups to the Same Lookup Table

Post by Tim Heckma » Thu, 17 May 2001 11:19:13



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

 
 
 

Multiple Lookups to the Same Lookup Table

Post by Fisbec » Thu, 17 May 2001 21:38:54


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).




> > 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

 
 
 

Multiple Lookups to the Same Lookup Table

Post by Tim Heckma » Thu, 17 May 2001 23:21:46



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

 
 
 

Multiple Lookups to the Same Lookup Table

Post by Fisbec » Thu, 17 May 2001 23:42:25


Thanks!  That worked perfectly.




> > 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

 
 
 

1. Lookup Tables to Lookup Tables

I am on a job converting an Oracle database to SQL Server.  We are making
some changes to the data model.  The database includes about 30 lookup
tables which supply necessary data choices to the main table.  In several
cases, there are lookup tables which, in turn, supply data to fields in the
lookup tables.  I haven't come across this before and wherever the
secondary lookup tables consist of only a few choices am recommending to
the client that they eliminate the secondary lookup table and use a
constraint instead on the field in the primary lookup table.  

Are lookup tables to lookup tables standard industry practice?  (My
instinct tells me no but I thought I'd check...)

Thanks.
--
Donna S.

(Remove no-spam from address before replying!)

2. BCPing Numeric Data

3. Lookup Behavior When Lookup Table is Empty

4. SQL problem : another owner's table

5. Problem: multiple joins to a single (lookup) table

6. DB2 6.1fp6 deathlocks,which fixlevel?

7. Insert Into Multiple tables using Lookup

8. Multiple Field Lookup Tables. PDOXW5.0

9. FP2.6: Multiple Lookups to One Table from Another

10. DTS, Lookups--Using a lookup in a DTS transform

11. is it possible to lookup a field value, based on a lookup that is based on another lookup, in a repeating field?

12. Multiple Lookups