How to pull out several rows with different parameters and a limit on the number in one request?

Question

SQL

The task is the following, to pull out with one request hotels where the country = United States and Spain
with a limit of 3 hotels from the country,
the code is something like this.
How can I set country limits?
SELECT *
FROM `hotels` `h`
INNER JOIN `catalog` `c`
ON `h`.`country` = `c`.`id`
WHERE `hc`.`label` IN ('United States', 'Spain ')

Answer 1

I think the scheme will be something like this.
In the where parameter, we make a new subquery from the same table.
We make an id selection by matching the name from the first selection with the name from the second and limit it to three results.
As a result, we should get three id for the main query, which will combine these records with other data.
The query might look something like this:
SELECT * FROM hotels a
WHERE a.id = (SELECT b.id FROM hotels b WHERE a.label = b.label LIMIT 3 )
And then inner join...
Such a query will go through all countries and take only three entries. If you need to go through only two countries, you need to add an additional restriction to any query through AND.