Databases
StackOverflow2013
Postsdbo
POrelevance search across multiple related tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POrelevance search across multiple related tables
    primarykey
    data
    text
    <p>I have a table called <code>cards</code> which has related tables <code>brigades</code> and <code>identifiers</code>. A single card can have multiple brigades and identifiers. I want to take a singe search such as <code>'purple king'</code> where 'purple' is a brigade and 'king' is an identifier, and find cards with those brigades and identifiers. This answer to a similar question, <a href="https://stackoverflow.com/a/9951200/633513">https://stackoverflow.com/a/9951200/633513</a>, showed how you can fulltext search across multiple tables. I'd like to do the same thing, except i just want related matches. Is this possible?</p> <p>Table Structures:</p> <pre><code>Cards: id as INT, title as VARCHAR(50) Brigades: id as INT, brigade as VARCHAR(50) Identifier: id as INT, identifier as VARCHAR(50) </code></pre> <p>Join Tables:</p> <pre><code>CardBrigades: id as INT, card_id as INT, brigade_id as INT CardIdentifiers: id as INT, card_id as INT, identifier_id as INT </code></pre> <p>Sample Identifiers:</p> <pre><code>Angel Animal Archer Army Assyrian Babylonian Based on Prophecy Beast Bows, Arrows, Spears, Javelins and Darts Canaanite 'Capture' in Special Ability 'Censer' in Title Chief Priest Child of Leah Commander Connected to David Connected to Demons 'Covenant' in Title 'David' in Title 'David' in Verse Deacon Prince </code></pre> <p>Sample Brigades:</p> <pre><code>None Black Brown Crimson Gold (Evil) Gray Orange Pale Green Multi (Evil) Blue Gold (Good) Green Purple Red Silver Teal White Multi (Good) Multi </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USLordZardeck
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POrelevance search across multiple related tables
      1. USLordZardeck
      1. VTFavorite
    1. COThat table structure doesn't actually show the relation. Does Cards have columns for Brigades.id and Identifier.id values? Or does Bridgades of a Cards.id value? Something seems not right.
      singulars
      1. POrelevance search across multiple related tables
      1. USIlion
    2. COCan you provide some sample data? Do the VARCHAR fields just include 'purple' etc or might they also include 'purple heart' that you also want to match on.
      singulars
      1. POrelevance search across multiple related tables
      1. USliquorvicar
    3. CO@Ilion sorry, I had forgot about the join tables. I updated the question with them.
      singulars
      1. POrelevance search across multiple related tables
      1. USLordZardeck
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload