CODEWITHSUNDEEP
sqldatabasesqliteduplicates
Dark Inspiration
Dark Inspiration

Reputation: 19

Sqlite select duplicate values from two columns

I have a work for school and I have this table (not complete I simplified it):

email           region      card         quantidade
--------------  ----------  -----------  ----------
[email protected]  EU          Flamestrike  2
[email protected]  EU          Fireball     2
[email protected]  EU          Polymorph    3
[email protected]  EU          Water Eleme  6
[email protected]  EU          Arcane Expl  3
[email protected]  EU          Frostbolt    2
[email protected]  EU          Arcane Miss  8
[email protected]  EU          Mirror Imag  2
[email protected]  EU          Archamage A  1
[email protected]  EU          Pyroblast    2
[email protected]  US          Pyroblast    1
[email protected]  EU          Kirin Tor M  3
[email protected]  EU          Goblin Blas  2
[email protected]  EU          Unstable Po  11
[email protected]  EU          Iron sensei  2
[email protected]  EU          Shady Deale  2
[email protected]  EU          Backstab     5
[email protected]  EU          Deadly Pois  3
[email protected]  EU          Deathwing    2
[email protected]  EU          Nefarian     2
[email protected]  EU          Volcanic Dr  2
[email protected]  EU          Twilight Dr  2
[email protected]  EU          Cenarius     2
[email protected]  EU          King Krush   2
[email protected]  EU          Archmage An  2
[email protected]  EU          Tirion Ford  2
[email protected]  EU          Prophet Vel  2
[email protected]  EU          Edward VanC  2
[email protected]  EU          Al'Akir the  2
[email protected]  EU          Lord Jaraxx  2
[email protected]  EU          Grommash He  2
[email protected]  EU          Dr.Boom      2
[email protected]  EU          Mimiron's H  2
[email protected]  EU          Bolvar Ford  2

And want to output this from a query:

email           card                 region
--------------  -------------------  ----------
[email protected]  Grommash Hellscream  EU
[email protected]  Grommash Hellscream  US
[email protected]  Lord Jaraxxus        EU
[email protected]  Lord Jaraxxus        AS
[email protected]  Mind Control         EU
[email protected]  Mind Control         US
[email protected]  Pyroblast            EU
[email protected]  Pyroblast            US
[email protected]  Shady Dealer         EU
[email protected]  Shady Dealer         US
[email protected]  Unstable Portal      EU
[email protected]  Unstable Portal      US
[email protected]  Varian Wrynn         EU
[email protected]  Varian Wrynn         US

i.e: I want to select all the duplicates from email and card and show the region of the duplicate card/email

I tried this query but it gives me a syntax error: SELECT email, card, region FROM player_card WHERE (email,card) IN (SELECT email,card FROM player_card GROUP BY email,card HAVING count(*) > 1) ORDER BY card;

Table creation statement:

CREATE TABLE player_card
(email VARCHAR(30),
region VARCHAR(2) CHECK (region IN("US", "EU", "AS")),
card VARCHAR(30),
quantidade INTEGER NOT NULL,
PRIMARY KEY(email, region, card),
FOREIGN KEY(email) REFERENCES player(email) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(region) REFERENCES player(region),
FOREIGN KEY(card) REFERENCES card(name)
);

Upvotes: 0

Views: 1490

Answers (1)

Mauri
Mauri

Reputation: 1245

I could get the result by using join instead of the in operator:

SELECT p.email, p.card, p.region FROM player_card p join 
(SELECT email,card, count(*) as cnt FROM player_card GROUP BY email,card) t 
on p.email = t.email and p.card=t.card where t.cnt>1 
ORDER BY p.card;

live example

Upvotes: 2

Related Questions