MTGProxyPrinter

Interesting Card database queries
Login

Below are some interesting analysis SQL queries against the card database

Beware: Running these queries may take a few minutes to complete.

Cards with updated, localized card names.

The query returns all card names of non-English cards that changed their translated name in newer sets. The filter against english_names removes all cards for which the card name is missing and replaced with their English name as a placeholder.

WITH english_names (name) AS (
    SELECT card_name
    FROM FaceName
    JOIN PrintLanguage USING (language_id)
    WHERE "language" = 'en')

SELECT oracle_id, "language", P1.card_name, P1.set_name, P2.card_name, P2.set_name
  FROM AllPrintings AS P1
  JOIN AllPrintings AS P2 USING ("language", oracle_id, face_number, is_front)
  WHERE P1.scryfall_id < P2.scryfall_id
    AND P1.card_name <> P2.card_name
    AND P1.card_name NOT IN (SELECT name FROM english_names)
    AND P2.card_name NOT IN (SELECT name FROM english_names)
  GROUP BY P1.card_name, P2.card_name
  ORDER BY P1.language, P1.card_name;

Two or more distinct Cards having the same name

Sometimes, two different cards get translated to the same name in a given language, probably due to independent translation teams. As of writing this, the query returns 128 results. The first column creates a Scryfall search link that shows the list of clashing cards.

WITH english_names(name) AS (
    SELECT card_name
    FROM FaceName
    JOIN PrintLanguage USING (language_id)
    WHERE "language" = 'en')

SELECT printf('https://scryfall.com/search?q=l:%s+!%%22%s%%22&unique=prints&as=checklist',
              language, replace(card_name, ' ', '%20')) AS scryfall_search,
    language, card_name, P1.set_name, P1.oracle_id, P2.set_name, P2.oracle_id
  FROM AllPrintings AS P1
  JOIN AllPrintings AS P2 USING ("language", card_name, face_number, is_front)
  WHERE P1.oracle_id < P2.oracle_id
  AND P1.scryfall_id < P2.scryfall_id
  AND card_name NOT IN (SELECT name FROM english_names)
  GROUP BY "language", card_name
  ORDER BY language, card_name;