Thomas David Baker Thomas David Baker - 1 month ago 10
SQL Question

Query on both component parts and aggregation

I'm using sqlite3. I'd prefer to continue using it but switching is not out of the question.

I have two tables:

card face
---- ----
id id
card_id
name
position


A
card
has one or more entries in
face
.

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

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, ' // ')
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 (?)


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!

Answer

Try this version:

HAVING SUM(CASE WHEN name IN (?) THEN 1 ELSE 0 END) > 0

Do note that parameters cannot match lists. If you want to pass in a comma delimited list, you can try:

HAVING SUM(CASE WHEN ',' || name || ',' LIKE '%,' || ? || ',%' THEN 1 ELSE 0 END) > 0
Comments