Join multiple colums in 1 table to 1 lookup table

[csutton]

Ok, I've been searching the forum on here looking for a clear answer (or the hint of one) and I seem not to be able too.. so I'm desperately now seeking the help of you all.

I have 2 basic tables:
tbl_DCInfo
id autonumber
empname text
offense1 integer
offense2 integer
offense3 integer

tbl_offensecodes
id autonumber
offensedescr text


Okay, offense1, 2, and 3 all link to tbl_offensecodes via the ID #. What I need to return is:

itbl_DCInfo.id, empname, offensedescr (for offense1), offensedescr (for offense2), offensedescr (for offense3)

The ID # is stored for each offense1, 2, and 3.

PLLLLLEASE help

Chris Sutton

http://www.ruoktech.com

 

[Golom]

Select D.ID, D.empname, 

       (Select C.offensedescr 
        From tbl_offensecodes C
        Where C.Offense1 = D.ID) As Offence1,

       (Select C.offensedescr 
        From tbl_offensecodes C
        Where C.Offense2 = D.ID) As Offence2,

       (Select C.offensedescr 
        From tbl_offensecodes C
        Where C.Offense3 = D.ID) As Offence3

From tbl_DCInfo As D

 

[Golom]

Sorry ... got everything backward

Select D.ID, D.empname, 

       (Select C.offensedescr 
        From tbl_offensecodes C
        Where D.Offense1 = C.ID) As Offence1,

       (Select C.offensedescr 
        From tbl_offensecodes C
        Where D.Offense2 = C.ID) As Offence2,

       (Select C.offensedescr 
        From tbl_offensecodes C
        Where D.Offense3 = C.ID) As Offence3

From tbl_DCInfo As D

 

[lespaul]

or:

SELECT D.ID, D.empname, O1.Offensedescr, O2.OffenseDescr, O3.OffenseDescr
FROM tbl_DCInfo As D
INNER JOIN tbl_offensecodes O1 ON D.offense1 = O1.ID
INNER JOIN tbl_offensecodes O2 ON D.offense2 = O2.ID
INNER JOIN tbl_offensecodes O3 ON D.offense3 = O3.ID

you may also want to read Fundamentals of Relational Database Design. Having fields named offense1, offense2, etc. breaks the rules of normalization. What are you going to do when the powers that be decide that you should have all the way up to offense99? You'll have to change every query to add offense4, offense5, ...offense99.


Leslie

Have you met Hardy Heron?

 

[csutton]

Thank you both for the information. I understand the normalization part. I was simply trying to do something quick and dirty today to have it done due to timing. I don't use Access ever, but I needed a quick DB so that's why I went this route..

thank you again!!

Chris Sutton

http://www.ruoktech.com