sql max of count 2 tables
i've been searching for literally hours (1pm-11pm) for a solution to this SQL query I have to write. Basically, I have 2 tables and I have to select the ID from one table which has the maximum results in another. The second issue is there are 2 IDs. I can't quite explain what I mean because I'm that unsure but I can post my instructions and a link to the tables.
Any help will be greatly appreciated. I've also looked at a million other posts on SO and other places but even if it seems remotely relevant, I've no idea what changes to make to suit my needs.
So my task is as follows:-
Display the name and the telephone number of private owners which have more properties than anybody else.
The top table in the image shows the "properties for rent" table and the lower shows the "private owners".
In reference to the question, I need to use the primary key of the private owners table to count the number of properties that each private owner has available to rent and then display the details of the private owner(s) who has the most properties available - which by studying the data, is 2 private owners (CO87 and CO93).
Again, I'd appreciate any help at all with this, I've been pulling my hair out for the best part of 12 hours :/
Thanks in advance guys,
P.s - Just for the curious, this is one of an insane amount of SQL tasks for a university assignment =)
Edit:- The owner IDs are strings, not integers.
Your process should be:
- Get a count of properties for each owner
- Find the owner IDs with the max # of properties
- Find the owners with those owner ID.
Seems like this should work:
SELECT * FROM Owners WHERE OwnerID IN ( SELECT OwnerID FROM Properties GROUP BY OwnerID HAVING COUNT(*) = (SELECT COUNT(*) FROM Properties GROUP BY OwnerID ORDER BY COUNT(*) DESC LIMIT 1) )