title: How do I create a cross reference table/query for my data?
body: <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>