Find multiple rows that share one field value but have one unique value

Question

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 decks each pair of cards exist.

Let me know if I need to add anything else.

Answer 1

Join card_decks and cards twice.

SELECT pw.id AS pw_card_id,ss.id AS ss_card_id, d.id AS deck_id 
FROM decks d 
INNER JOIN card_decks ssd on ssd.deck_id = d.id
INNER JOIN card_decks pwd on pwd.deck_id = d.id
INNER JOIN cards pw ON pw.id = pwd.card_id AND pw.planeswalker
INNER JOIN cards ss ON ss.id = ssd.card_id AND ss.signature_spell ;

you can also group the result

SELECT pw.id AS pw_card_id,ss.id AS ss_card_id, count(*)
FROM decks d 
INNER JOIN card_decks ssd on ssd.deck_id = d.id
INNER JOIN card_decks pwd on pwd.deck_id = d.id
INNER JOIN cards pw ON pw.id = pwd.card_id AND pw.planeswalker
INNER JOIN cards ss ON ss.id = ssd.card_id AND ss.signature_spell 
GROUP BY 1,2 order by 3 desc, 2, 1;