noobprogrammer1987 noobprogrammer1987 - 6 months ago 59
MySQL Question

combine big join query with duplicate query

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.

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download