Query on both component parts and aggregation
<p>I'm using sqlite3. I'd prefer to continue using it but switching is not out of the question.</p>

<p>I have two tables:</p>

<pre><code>card      face
----      ----
id        id
          card_id
          name
          position
</code></pre>

<p>A <code>card</code> has one or more entries in <code>face</code>.</p>

<p>The canonical name of a card is the name of its faces concatenated with ' // ' in <code>position</code> order.</p>

<p>Example cards: 'Dark Ritual', a card with one face named 'Dark Ritual'. 'Research // Development', a card with two faces named 'Research' and 'Development'</p>

<p>How can I write a SELECT that finds the details of a card regardless of whether the search term is the canonical name or the name of one of the card's faces?</p>

<p>This is how far I got:</p>

<pre><code>SELECT id, GROUP_CONCAT(name, ' // ') 
FROM 
    (SELECT c.id, f.name
    FROM card AS c
    INNER JOIN face AS f ON c.id = f.card_id
    ORDER BY f.card_id, f.position)
GROUP BY id
HAVING GROUP_CONCAT(name, ' // ') IN (?)
OR name IN (?)
</code></pre>

<p>This finds the card I am looking for when ? = the canonical name ('Research // Development'.) It also finds what I am looking for when I supply the name of a card with only one face ('Dark Ritual') or the <em>second</em> of two face names ('Development'). It does <em>not</em> work when I supply the first of two face names ('Research'). I know the outer query can 'see' both names because they are both present in the GROUP_CONCAT form of the name. But querying on the first face name doesn't work in the HAVING.</p>

<p>Can you help me understand why? Thanks!</p>