joining 2 tables more than once

[chriso20]

Hey,

 

I have 2 tables - 'members' and 'types'.

 

Each member can have 3 'types'.

 

So the 'member' table has 4 columns; name, typeA, typeB & typeC.

 

and the 'type' table has 2 columns; id & type.

 

At the moment, to find the first of a members type i use the following SQL:

 

SELECT m.name, t.type

FROM members m

INNER JOIN type t

ON m.typeA = t.id

 

how can i find the other 2 types (typeB and typeC) for each member?

 

Thanks for any help!

 

p.s. I use a separate table because there's a LOT of members and a lot of text for the 'types'.

[aschk]

This is not a normalised layout. Normalise it first, otherwise you'll have to do the following:

SELECT m.name
      ,t1.type as 'typeA'
      ,t2.type as 'typeB'
      ,t3.type as 'typeC'
FROM members m
JOIN type t1 ON m.typeA = t1.id
JOIN type t2 ON m.typeB = t2.id
JOIN type t3 ON m.typeC = t3.id