Query help

[lemmin]

I have a table that looks something like this:

 

CatID    SuperCatID

1

2

3

4          1

5          1

6          2

7          3

8          3

 

Another table uses the CatID.

How can I get everything from either CatID or SuperCatID depending on the CatID. For example, if the CatID is 1, I want everything in the other table that has a CatID of 1,4 or 5.

 

Thanks for any help.

[Barand]

SELECT t.*

FROM othertable t

INNER JOIN atable c1 ON t.CatID = c1.CatID

LEFT JOIN atable c2 ON c1.SuperCatID = c2.CatID

WHERE c2.CatID = 1 OR c1.CatID = 1