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

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.

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;
``````