unable select unique values from a query

Question

Hello,

I want to select the name of a field on separate table, however I can only do this once. Cname1 will work , Cname2 will not. The problem is I would like a unique categoryChild name for m.categoryChildID2 but the only way I know how to reference this is with c.categoryChild BUT this is already in use with the m.categoryChildID1.

I know I can redo my table but I prefer not to if this is possible?

select c.categoryChild as cname1,c.categoryChild as cname2 
  FROM categoryChild as c, members as m 
 WHERE m.memberID=50 
   AND m.categoryChildID1=4 
   AND m.categoryChildID1=c.categoryChildID 
   AND m.categoryChildID2=5  m.categoryChildID2=c.categoryChildID

Thanks Brian

Answer 1

Join categoryChild table twice

SELECT c1.categoryChild as cname1,c2.categoryChild as cname2 
FROM members AS m,
JOIN categoryChild AS c1 ON(m.categoryChildID1 = c1.categoryChildID)
JOIN categoryChild AS c2 ON(m.categoryChildID2 = c2.categoryChildID)
WHERE m.memberID=50