94 lines
2.3 KiB
SQL
94 lines
2.3 KiB
SQL
-- Unowned Land
|
|
|
|
WITH
|
|
Cleaned_Card AS (
|
|
SELECT
|
|
C.card_id
|
|
, C.name
|
|
, C.type_line
|
|
, C.oracle_text
|
|
, C.color_identity
|
|
, C.produced_mana
|
|
, COALESCE(C.price_eur, C.price_usd, C.price_tix) AS price
|
|
FROM tcg.public.TCG_MTG_Card C
|
|
)
|
|
, Owned_Card AS (
|
|
SELECT C.name
|
|
FROM tcg.public.TCG_MTG_Card C
|
|
INNER JOIN tcg.public.TCG_MTG_Inventory I ON C.card_id = I.card_id
|
|
)
|
|
, Unowned_Land AS (
|
|
SELECT
|
|
CC.card_id
|
|
, CC.name
|
|
, ROW_NUMBER() OVER (
|
|
PARTITION BY
|
|
CC.name
|
|
, CC.oracle_text
|
|
ORDER BY
|
|
CC.price NULLS LAST
|
|
, CC.type_line
|
|
, CC.card_id
|
|
) AS index_card_price_in_card_reprints
|
|
FROM Cleaned_Card CC
|
|
LEFT JOIN Owned_Card OC ON CC.name = OC.name
|
|
WHERE
|
|
OC.name IS NULL
|
|
AND CC.type_line LIKE '%Land%'
|
|
)
|
|
SELECT
|
|
-- COUNT(*)
|
|
-- MAX(COALESCE(CC.price, 0.0))
|
|
CC.name
|
|
, CC.type_line
|
|
, REPLACE(CC.name, ' ', '+')
|
|
, CC.oracle_text
|
|
, CONCAT(
|
|
'https://magicmadhouse.co.uk/search.php?search_query='
|
|
, REPLACE(CC.name, ' ', '+')
|
|
, '&Filters=brand%3AMagic%253A%2520The%2520Gathering'
|
|
) AS url_mm
|
|
, CONCAT(
|
|
'https://www.chaoscards.co.uk/search/'
|
|
, REPLACE(CC.name, ' ', '+')
|
|
, '?sort=price_asc&in_stock=In+stock&brand=Magic+the+Gathering'
|
|
) AS url_cc
|
|
, CONCAT(
|
|
'https://www.bigorbitcards.co.uk/magic-the-gathering/search/'
|
|
, REPLACE(CC.name, ' ', '+')
|
|
, '/?order=product.price.asc'
|
|
) AS url_boc
|
|
, CC.color_identity
|
|
, CC.produced_mana
|
|
, CC.price
|
|
FROM Cleaned_Card CC
|
|
INNER JOIN Unowned_Land UL ON CC.card_id = UL.card_id
|
|
WHERE
|
|
UL.index_card_price_in_card_reprints = 1
|
|
AND ( -- Invalid colours
|
|
CC.color_identity NOT IN ('G, R', 'B, G', 'B, R', 'B, U')
|
|
)
|
|
AND CC.oracle_text NOT LIKE '%This land enters tapped unless you control a Mount or Vehicle.%'
|
|
ORDER BY
|
|
CC.price NULLS LAST
|
|
, CC.type_line
|
|
, CC.name
|
|
, CC.card_id
|
|
-- LIMIT 250
|
|
OFFSET 200
|
|
;
|
|
|
|
/*
|
|
SELECT
|
|
C.name
|
|
, C.set
|
|
FROM tcg.public.TCG_MTG_Card C
|
|
INNER JOIN tcg.public.TCG_MTG_Inventory I ON C.card_id = I.card_id
|
|
WHERE UPPER(C.type_line) LIKE '%SOLDIER%'
|
|
ORDER BY
|
|
C.set
|
|
, C.name
|
|
;
|
|
*/
|
|
|