<p>I have two simple tables in my database. A "card" table that contains Id, Name, and text of a card, and a "rulings" table which contains the Id of the card, and text detailing the rulings for the card.</p>

<p>Often enough in the ruling text, there is a reference to another card in the database. It is easy enough to find this in the text because each card is encapsulated within quotes in the text. It is not uncommon to have multiple cards referenced within the text of the ruling.</p>

<p>What I'd like to do is be able to create a cross reference table (or procedure if it is efficient enough) so that when I submit a query for a card, I can find all the ruling records that directly reference the card through the Id and get all of the ruling records where the card name is referenced in the text.</p>

<p>What would be the best way to approach this? My environment is SQL 2005, but any kind of "DB agnostic" solutions are greatly accepted here.</p>

I would recommend that you create another table that stores your references.  Then, create an insert and update trigger that maintains this table.  This way, you would have a faster query to return the data you are looking for.
I recognize that initially populating this table might be a little difficult, which is why I am showing some sample data (and query) below, that you can use to get you started.
```
```
Declare @Card Table(Id Int, Name VarChar(20), CardText VarChar(8000))

Declare @Ruling Table(CardId Int, CardRuling VarChar(8000))

Insert Into @Card Values(1, 'Card 1', 'This is the card ID = 1')
Insert Into @Card Values(2, 'Card 2', 'This is the card id = 2.')
Insert Into @Card Values(3, 'Card 3', 'This is the card id = 3.')

Insert Into @Ruling Values(1, 'This is the ruling for 1 which references "2"')
Insert Into @Ruling Values(2, 'This is the ruling for 2 which references nothing')
Insert Into @Ruling Values(3, 'This is the ruling for 3 which references "1" and "2"')

Declare @CardId Int
Set @CardId = 1

Select  * 
From    @Card As Card
        Inner Join @Ruling As Ruling
            On Card.Id = Ruling.CardId
        Left Join @Card As CardReferences
            On Ruling.CardRuling Like '%"' + Convert(VarChar(10), CardReferences.Id) + '"%'
```
```
EDIT:
The reason I suggested another table is because you will likely be disappointed with the performance of this query, especially for large tables.