Want to use LIMIT but its not appropriate for my needs

Databases
1

I have a situation where a person arrives on a page having clicked a theme on the previous page, the problem I am having is when they click ‘Latest Additions’ which should show the last 10 entires in the database, but rather than showing the hotels, I need to show the countries associated with those hotels.

So I have this for starters -


if ($selectCategory=="Latest Additions") {	
	
$r=mysql_query("SELECT DISTINCT(Id_Cntry), Nom_Cntry FROM tbl_countries LEFT JOIN tbl_hotels ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE (tbl_hotels.Act_Hot='1') ORDER BY tbl_countries.Nom_Cntry");
}

It works fine in that it outputs all the countries, but what I need is to use this too -


$r=mysql_query("Select * from tbl_hotels WHERE Act_Hot=1 ORDER ORDER by tbl_hotels.Id_Hot LIMIT 0,10 DESC");

So basically I ‘select’ the countries associated with the hotels limited by 10 and order by Nom_Cntry.

Can you see my problem, I need to limit the hotels to 10 and show the countries associated with it, rather than limit the countries to 10.

2

Wouldn’t you just limit your prior query?

$r=mysql_query("SELECT DISTINCT(Id_Cntry), Nom_Cntry FROM tbl_countries LEFT JOIN tbl_hotels ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE (tbl_hotels.Act_Hot='1') ORDER BY tbl_countries.Nom_Cntry LIMIT 0, 10");

Or does that not produce the wanted results?

3

Hi cpradio,

No tried that and basically what happens there is that it LIMIT’s the countries to 10, when basically what I need to happens is not LIMIT the countries, but LIMIT the hotels to 10 and it displays the countries associated with those hotels, regardless of how many countries there are.

It could only show 1 country if say the last 10 hotels uplaoded are all from Egypt, but if say the last 10 hotels are from Spain, Egypt & UK, then only 3 countries will show for the 10 hotels selected.

Does that make sense to you, as I cant work out how to do it code wise.

4

I think this might do it:

SELECT tbl_countries.Id_Cntry, tbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot
WHERE tbl_hotels.Act_Hot='1' ORDER ORDER by tbl_hotels.Id_Hot DESC LIMIT 0,10

What you’re doing here is to select on the hotel table (even though we’re not displaying any columns from it), and joining the countries table to get the country name and ID.

5

That was going to be my next suggestion, change the order of the tables in your query. If that still fails, give me a sampling of your data (50 countries, and their respective hotels – attach a CSV or something) and I’ll play with it.

In the meantime, I’m moving this to the DB forum.

6

I used this:

[SQL]
SELECT tbl_countries.Id_Cntry, tbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE tbl_hotels.Act_Hot=‘1’ ORDER BY tbl_hotels.Id_Hot DESC LIMIT 0,10
[/SQL]

It seems to have worked yes thank you cpradio, but it has outputted some of the countries a few times, so its fine then to use GROUP instead of ORDER isnt it.

But thanks cpradio, there no way on Earth I would have got that.

Cheers

7

If you only want each country to appear once in the result (even if multiple hotels in the top 10 are from that country) then yes, you can add GROUP BY tbl_countries.Nom_Cntry to your query.

8

No didnt seem to work that -


SELECT tbl_countries.Id_Cntry, tbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE tbl_hotels.Act_Hot='1' GROUP BY tbl_hotels.Id_Hot DESC LIMIT 0,10

That didnt change the country output, so changed it too


SELECT tbl_countries.Id_Cntry, tbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE tbl_hotels.Act_Hot='1' GROUP BY tbl_countries.Nom_Cntry DESC LIMIT 0,10

and it returned to outputting 10 countries

working on ‘Latest Additions’ at the mo from the browse by themes, but the all inclusive works too, just not connected the results up properly yet

http://devchecksafetyfirst.csf.dcmanaged.com/category_Result.php?Category=Latest Additions

9

I just tried to JOIN another group on, but thats not working - but I might be completely wrong as it was based on a guess -


$r=mysql_query("SELECT tbl_countries.Id_Cntry, tbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE tbl_hotels.Act_Hot='1' JOIN (GROUP BY tbl_countries.Nom_Cntry) GROUP BY tbl_hotels.Id_Hot DESC LIMIT 0,10");
10

Sorry cpradio, i missed your reply, will get to it now.

11

How do I reply and attach files, or shall I send them to your email address.

This is where I am at at the mo -


$r=mysql_query("SELECT tbl_countries.Id_Cntry, tbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) JOIN (GROUP BY tbl_countries.Nom_Cntry) WHERE tbl_hotels.Act_Hot='1' ORDER BY tbl_hotels.Id_Hot DESC LIMIT 0,10");
12

Click on Go Advanced and you can add an attachment.

13

Hopefully this is what you want.

Tricky one this isnt it.

Cheers cpradio

14

Would something like this work (I limited the returned values from tbl_Hotels in a sub-query).


SELECT Nom_Cntry
  FROM tbl_countries c
  JOIN (SELECT IdCntry_Hot
          FROM tbl_hotels
         WHERE Act_Hot = '1'
		 LIMIT 0, 10) sq ON sq.IdCntry_Hot = c.Id_Cntry
15

Okay, so let’s start back at the beginning. You want exactly 10 records. Those 10 records need to be the latest hotels and include the country the hotel resides in. Right?

SELECT
  tbl_hotels.*,
  tbl_countries.*
FROM tbl_hotels
  LEFT JOIN tbl_countries ON (tbl_countries.Id_Cntry = tbl_hotels.IdCntry_Hot)
ORDER BY tbl_hotels.Id_Hot DESC
LIMIT 0, 10

A hotel should only exist in 1 country. So unless your data is bad, the above should return what you are asking for.

[edit]My Proof of Concept in SQL Server:

CREATE TABLE #tbl_countries
(
	Id_Cntry INT,
	Nom_Cntry VARCHAR(50),
	Region VARCHAR(100),
	Misc INT
)

INSERT INTO #tbl_countries VALUES ('1', 'Spain', 'EU', '1')
INSERT INTO #tbl_countries VALUES ('2', 'Dominican Republic', 'Caribbean Island', '3')
INSERT INTO #tbl_countries VALUES ('3', 'Mexico', 'Central America', '5')
INSERT INTO #tbl_countries VALUES ('4', 'Tunisia', 'North Africa', '2')
INSERT INTO #tbl_countries VALUES ('5', 'Egypt', 'Middle East / North Africa', '2')

CREATE TABLE #tbl_hotels
(
	Id_Hot INT,
	IdCntry_Hot INT,
	HotelName VARCHAR(100)
)

INSERT INTO #tbl_hotels VALUES ('1', '1', 'Apts. Tamaimo Tropical')
INSERT INTO #tbl_hotels VALUES ('2', '2', 'Aparthotel Be Live La Calita')
INSERT INTO #tbl_hotels VALUES ('3', '3', 'Apartments Be Smart Madrid Albufera')
INSERT INTO #tbl_hotels VALUES ('4', '4', 'M/S Semiramis III')
INSERT INTO #tbl_hotels VALUES ('5', '5', 'The Grand Bliss')
INSERT INTO #tbl_hotels VALUES ('6', '1', 'Pez Espada')
INSERT INTO #tbl_hotels VALUES ('7', '2', 'Eurostars Rembrandt Classic - Amsterdam')
INSERT INTO #tbl_hotels VALUES ('8', '3', 'Occidental Abou Sofiane')
INSERT INTO #tbl_hotels VALUES ('9', '4', 'Eurostars Das Letras')
INSERT INTO #tbl_hotels VALUES ('10', '5', 'Hotel Eurostars Astoria')
INSERT INTO #tbl_hotels VALUES ('11', '1', 'Sentido M/S Nile Saray')
INSERT INTO #tbl_hotels VALUES ('12', '2', 'Majesty Marina Vista')
INSERT INTO #tbl_hotels VALUES ('13', '3', 'Sofitel Karnak Hotel')
INSERT INTO #tbl_hotels VALUES ('14', '4', 'Hilton Alexandria Green Plaza')
INSERT INTO #tbl_hotels VALUES ('15', '5', 'Playa Mia Grand Beach Park')
INSERT INTO #tbl_hotels VALUES ('16', '1', 'Tiran Island Hotel')
INSERT INTO #tbl_hotels VALUES ('17', '2', 'H10 Conquistador')
INSERT INTO #tbl_hotels VALUES ('18', '3', 'Parador de Baiona')
INSERT INTO #tbl_hotels VALUES ('19', '4', 'Barceló Isla Canela')
INSERT INTO #tbl_hotels VALUES ('20', '5', 'Iberostar Founty Beach')

SELECT * FROM #tbl_countries
SELECT * FROM #tbl_hotels

SELECT 
	TOP 10
	#tbl_hotels.Id_Hot,
	#tbl_hotels.HotelName,
	#tbl_countries.Nom_Cntry
FROM #tbl_hotels 
	LEFT JOIN #tbl_countries ON (#tbl_countries.Id_Cntry = #tbl_hotels.IdCntry_Hot) 
ORDER BY #tbl_hotels.Id_Hot DESC

DROP TABLE #tbl_countries
DROP TABLE #tbl_hotels

Produced:

20	Iberostar Founty Beach	Egypt
19	Barceló Isla Canela	Tunisia
18	Parador de Baiona	Mexico
17	H10 Conquistador	Dominican Republic
16	Tiran Island Hotel	Spain
15	Playa Mia Grand Beach Park	Egypt
14	Hilton Alexandria Green Plaza	Tunisia
13	Sofitel Karnak Hotel	Mexico
12	Majesty Marina Vista	Dominican Republic
11	Sentido M/S Nile Saray	Spain

[/edit]

16

cpradio, that’s what I thought too - hence the query we discussed in posts #4 and #5… but I think multichild wants to remove duplicate countries from that list, so your result list should only be 5 items long… at least, that’s how I understand it.

17

yes it worked and as fretburner pointed out it is outputting duplicate countries.

http://devchecksafetyfirst.csf.dcmanaged.com/category_Result.php?Category=Latest Additions

So what it needs to do is group those countries somehow

18

Okay, so in SQL (SQL Server), the following produces the distinct 5 countries from the last 10 hotels.


SELECT
	DISTINCT(Nom_Cntry)
FROM (
	SELECT 
		TOP 10
		#tbl_hotels.Id_Hot,
		#tbl_hotels.HotelName,
		#tbl_countries.Nom_Cntry
	FROM #tbl_hotels 
		LEFT JOIN #tbl_countries ON (#tbl_countries.Id_Cntry = #tbl_hotels.IdCntry_Hot) 
	ORDER BY #tbl_hotels.Id_Hot DESC
) q

So I think Mitt Dave is on the right track here, so something like:


SELECT DISTINCT Nom_Cntry
  FROM tbl_countries c
  JOIN (SELECT IdCntry_Hot
          FROM tbl_hotels
         WHERE Act_Hot = '1'
         ORDER BY Id_Hot DESC
		 LIMIT 0, 10) sq ON sq.IdCntry_Hot = c.Id_Cntry

Or


SELECT
	DISTINCT(Nom_Cntry)
FROM (
	SELECT 
		tbl_countries.Nom_Cntry
	FROM tbl_hotels 
		LEFT JOIN tbl_countries ON (tbl_countries.Id_Cntry = tbl_hotels.IdCntry_Hot) 
	ORDER BY tbl_hotels.Id_Hot DESC
	LIMIT 0, 10
) q
19

Thats done it by the looks, thanks guys, i in honesty would never have got it.

It never ceases to amaze me that the better I get there is always another level.

Thank you

20

Wrong blue boy, but I’ll take the compliment :smiley:

multichild, glad you got it working :slight_smile: