GROUP BY all columns?-CodePudding

New to MySQL, couldn't find the right answer here.

I have these tables: Passengers, and Tickets. I need to show all the data of the passengers that bought 5 tickets, no just the ID

I've managed to do this, but I need all the data of the passengers.

SELECT Passenger.IdPassenger
FROM Passengers
JOIN Ticket ON Passenger.IdPassenger = Ticket.IdPassenger
GROUP BY Passenger.IdPassenger
HAVING COUNT(*) = 5;

The only way mysql let me show all the data of the passengers is doing SELECT * and then in the GROUP BY clause put EVERY column, is there a way to do a group by all like group by * ?

CodePudding user response：

GROUP BY the Tickets table in a derived table, to get 5 tickets id's. JOIN:

select p.*
from Passengers p
join (SELECT IdPassenger
      FROM Ticket
      GROUP BY IdPassenger
      HAVING COUNT(*) = 5) t
   ON p.IdPassenger = t.IdPassenger

CodePudding user response：

You can use id's in subquery to get all data :

select *
  from Passengers p
 where p.IdPassenger in (select Ticket.IdPassenger
                           from Ticket
                          group by Ticket.IdPassenger
                         having count(*) = 5)