combine big join query with duplicate query

Question

I am struggling to combine these two sql queries.

SELECT players.player_id, players.tag, players.game_race,
       earnings.tournament, earnings.player, earnings.position,
       tournaments.tournament_id, tournaments.region
  FROM players
  JOIN earnings ON players.player_id = earnings.player 
  JOIN tournaments ON earnings.tournament = tournaments.tournament_id
  where position = 1
ORDER BY players.player_id;

SELECT tournament, earnings.player, prize_money, position 
FROM earnings 
join (
    SELECT player FROM earnings 
    where position = 1 group by player having count(player) > 3) 
as DupPlayer
on earnings.player=DupPlayer.player
where position = 1;

So I tried with a union.

(SELECT players.player_id, players.tag, players.game_race,
       earnings.tournament, earnings.player, earnings.position,
       tournaments.tournament_id, tournaments.region
  FROM players
  JOIN earnings ON players.player_id = earnings.player 
  JOIN tournaments ON earnings.tournament = tournaments.tournament_id
  where position = 1
ORDER BY players.player_id
)
UNION
(
SELECT tournament, earnings.player, prize_money, position 
FROM earnings 
join (
    SELECT player FROM earnings 
    where position = 1 group by player having count(player) > 3) 
as DupPlayer
on earnings.player=DupPlayer.player
where position = 1
);

Then I got the error message "The used SELECT statements have a different number of columns". How can I go about fixing this? Is there a better method than union? I really don't wanna change my select statements because I want to see all those columns.

Md. Suman Kabir · Accepted Answer · 2017-11-24 14:53:30Z

2

Combine the queries like below :

SELECT players.player_id, players.tag, players.game_race,
       earnings.tournament, earnings.player, earnings.position,
       tournaments.tournament_id, tournaments.region
  FROM players
  JOIN earnings ON players.player_id = earnings.player 
  JOIN (SELECT player FROM earnings 
        where position = 1 group by player having count(player) > 3) 
        as DupPlayer on earnings.player=DupPlayer.player
  JOIN tournaments ON earnings.tournament = tournaments.tournament_id
  where position = 1
ORDER BY players.player_id;

answered Nov 24, 2017 at 14:53

Md. Suman Kabir

5,4615 gold badges30 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

combine big join query with duplicate query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related