I have tables cards, decks, and a join table card_decks. I want to find the count of a unique set of pairs of cards where the a given attribute is true. I feel like I'm close but there is another step I am missing.
SELECT c.id card_id, d.id deck_id
FROM cards c
INNER JOIN card_decks cd
ON c.id = cd.card_id
INNER JOIN decks d on cd.deck_id = d.id
WHERE planeswalker = true OR signature_spell = true;
This gives me data that is a step closer.
card_id | deck_id
---------+---------
9137 | 2787
9138 | 2787
9182 | 2788
9183 | 2788
9207 | 2789
9218 | 2789
9233 | 2790
9234 | 2790
9285 | 2791
9286 | 2791
9360 | 2793
9361 | 2793
What I don't understand is how to make 9317
and 9138
a unit that I can see if the pair exists in any other decks. I know there is a CONCAT
function, but I'm not sure how to CONCAT
two different rows like this.
Or more simply I want to find the count of all pairs of cards where a pair is one card with planeswalker
true and one card with signature_spell
true per deck.
EDIT 1:
Sorry about the poorly stated original question.
Here is the sample data:
create table cards (
id INT,
name VARCHAR(50),
planeswalker BOOLEAN,
signature_spell BOOLEAN
);
insert into cards (id, name, planeswalker, signature_spell) values
(1, 'Norby', false, true),(2, 'Johanna', true, false),
(3, 'Killian', false, true), (4, 'Madalyn', false, false),
(5, 'Queenie', true, false),(6, 'Mort', true, false),
(7, 'Nedda', false, true),(8, 'Pennie', true, false),
(9, 'Fabien', false, true),(10, 'Fiann', true, false);
create table decks (
id INT,
name VARCHAR(50)
);
insert into decks (id, name) values
(1, 'Buzzshare'),(2, 'Mymm'),(3, 'Pixope'),(4, 'Zazio'),
(5, 'Shufflester'),(6, 'Youbridge'),(7, 'Avavee'),
(8, 'Thoughtblab'),(9, 'Skiptube'),(10, 'Eare');
create table card_decks (
id INT,
card_id INT,
deck_id INT
);
insert into card_decks (id, card_id, deck_id) values
(1, 1, 1),(2, 2, 1),(6, 7, 2),(4, 6, 2),
(3, 3, 3),(5, 5, 3),(7, 1, 4),(8, 2, 4);
The result I would like for the given sample data:
pw_id | ss_id | count
---------+---------+----------
1 | 2 | 2
6 | 7 | 1
5 | 3 | 1
Where pw_id
is a card id with planeswalker = true
and ss_id
is a card id with signature_spell = true
, and the count is how many deck
s each pair of cards exist.
Let me know if I need to add anything else.