Two table columns refer to one primary key problem

[samvb]

Ola,

Am having problem with this design. I have tableTips (tipid (tipid (Primary, auto number),tipname ). And another table tableRelatedTip s (tipid1,tipid2) .

Both tipid1 and tipid2 refer to tableTips. A combination of both must be unique. So when I am seeing tipid from tableTips, I want to see related tips.

Data Wise:

1 Tip 100
2 Tip 200
3 Tip 300
4 Tip 400

And tableRelatedTip s

1 3 (1 and 3 are related)
2 1 (2 and 1 are related)

Is my design bad? How can I get list of related tips for a specific tip? E.G. if tip 1, I need to get tips 3 and 2.

I tried both IN and EXISTS (and NOT EXISTS) but I end up getting twice the row numbers in tableTableTips or none at all.

Any tips?

[Rabbit]

Given that we have no idea what you're using the data for, or what it's supposed to represent, the design looks fine.

To find the ones that have 1 in either field, you can query exactly that in the WHERE clause.

SELECT *
FROM tableRelatedTips
WHERE
   tipid1 = 1 OR
   tipid2 = 1

If that's not what you're looking for, then you will need to explain more clearly what it is you're trying to achieve.