sql query string logical problem

[marcmm]

Ok, I'm having a big problem with an sql string for a query.

I want to implement a methode that allows the user to search for hotels by entering the minimal price ( so if they enter say 20 it will only display hotels that have prices above 20 ).

SO I setup a textbox to colect the value then I was to create the sql string to make the query. Well at that point everything whent wrong.

Well this is my sql string B4 implementing the minimal price search:

$sql_hstring="SELECT DISTINCT tables.names
		        FROM base table, satelite1, satelite2, satelite3
			WHERE ((basetable.city = satelite1.ID )
			AND (satelite1.name = satelite2.ID)
			AND (basetable.something=satelite3.ID))
                        ORDER BY localitate.den_localitate ASC";

basicly it means I have a few many to 1 conections already in the condition area of the query. the query works perfectly at this point.

Well, the hotel prices are stored in yet another table ( because I have more then one price for a hotel ).

SO the new query would logically be like this:

$sql_string="SELECT DISTINCT tables.names
		        FROM base table, satelite1, satelite2, satelite3, prices
			WHERE ((basetable.city = satelite1.ID )
			AND (satelite1.name = satelite2.ID)
                        AND (basetable.hotel_id = prices.ID_hotel)
			AND (basetable.something=satelite3.ID))
                        ORDER BY localitate.den_localitate ASC";

The link is yet another many to 1 link... linking the unick ID hotel in the base table with a column that identifiew wich price belongs to wich hotel.

at the moment the prices table looks like this:

ID hotel_ID price
1 1 xx
2 1 xy
3 2 yy

meaning thet the first two prices belong to the hotel with the 1 identifyer in the base table

and the 3rd price belongs to the htoel with the 2 identifyer in the basetable.

but addint the extra many to 1 link in the condition area of the string ruins everything...

without specifying any conditions o nthe user's behalf ( meaning it should display ALL the hotels... and there are about 8 of them in the test DB at the moment ) it displays 3 results... namelly it displays the 1 identifyed hotel twice and the 2 identifyed hotel 1 time... and this is DESPITE there beying a DISTINCT select at the beginning of the querry.

i'm sure that this extra condition:

AND (basetable.hotel_id = prices.ID_hotel)

ruins the logical flow of the string... but what can I do about it?
I can't imagine a way in wich to make a separate query to handle the price since the price HAS to be part of the main query... but adding it to the main query ruins the results... can anybody give me an idea about what I coudl do?

[martin5211]

Use a INNER JOIN statement in your SQL query, placing it before WHERE . You may be able to link a common ID field between the two tables and show a result into one set.

[marcmm]

Use a INNER JOIN statement in your SQL query, placing it before WHERE . You may be able to link a common ID field between the two tables and show a result into one set.

well yes... but my problem is that it's displaying the same hotel twice ( and if the prices table will have for example 4 prices atributed to the same hotel it will display that hotel 4 times ) despite there beying a DISTINCT keyword set at the beginning of the query string.

at this point I just need to prevent any duplicate posts. To the best of my knowledge DISTINCT is supposed to handle that... but for some reason it backfires in this example and I cannot understand why.

I need it to display results only once.