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?
Unsolved 
