I'm using sqlite3. I'd prefer to continue using it but switching is not out of the question.
I have two tables:
has one or more entries in
The canonical name of a card is the name of its faces concatenated with ' // ' in
Example cards: 'Dark Ritual', a card with one face named 'Dark Ritual'. 'Research // Development', a card with two faces named 'Research' and 'Development'
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?
This is how far I got:
SELECT id, GROUP_CONCAT(name, ' // ')
(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 (?)
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 second
of two face names ('Development'). It does not
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.
Can you help me understand why? Thanks!