select query question

[Anon]

Hi,

Hope you guys can help, I have a query which is working just fine -

SELECT r.ResortID,
s.ToSellAcceptPrice,
r.ResortName,
b.BedroomType,
w.WeekNumber,
c.ResortCountry

FROM T_ToSellInventory AS s,
T_Resort AS r,
T_Bedroom AS b,
T_Week AS w,
T_Country AS c

WHERE r.ResortID = s.ToSellResortID
AND r.ResortName LIKE '%$resort_name%'
AND b.ID = s.ToSellBedroomID
AND w.ID = s.ToSellWeekID
AND c.ID = r.ResortCountryID

ORDER BY ResortName

it's to search a database of timeshares.

But I also want to look up in a table called T_Resort_Info to see if the resort(s) found in T_Resort has any extra info. Some resorts do, some do not.

I have tried just adding it on the query above using AND on a RelatedResortID in the T_Resort_Info table but then if there isn't an entry for it, nothing is returned at all.

Basically I want the results returned whether there is an entry in T_Resort_Info or not for the resort(s). I am currently using a nested query which I'm sure there is a better way because it slows it down quite a bit!

Thanks in advance for any help, hope I've made myself clear.

Best Regards,

Ian Donovan

[bastien]

SELECT r.ResortID,
s.ToSellAcceptPrice,
r.ResortName,
b.BedroomType,
w.WeekNumber,
c.ResortCountry,
ri.*

FROM T_ToSellInventory AS s,
T_Resort AS r,
T_Bedroom AS b,
T_Week AS w,
T_Country AS c,
T_Resort_Info as ri

WHERE r.ResortID = s.ToSellResortID
AND r.ResortName LIKE '%$resort_name%'
AND b.ID = s.ToSellBedroomID
AND w.ID = s.ToSellWeekID
AND c.ID = r.ResortCountryID
and c.ID = ri.Resort_InfoID

ORDER BY r.ResortName
this will pull all the info from the t_resort_info table....

hth
Bastien

[Anon]

yeah.. sorry didnt think i was being too clear, i want this query-

SELECT r.ResortID,
s.ToSellAcceptPrice,
r.ResortName,
b.BedroomType,
w.WeekNumber,
c.ResortCountry,
ri.ResortInfoID

FROM T_ToSellInventory AS s,
T_Resort AS r,
T_Bedroom AS b,
T_Week AS w,
T_Country AS c,
T_Resort_Info AS ri

WHERE r.ResortID = s.ToSellResortID
AND r.ResortName LIKE '%'
AND b.ID = s.ToSellBedroomID
AND w.ID = s.ToSellWeekID
AND c.ID = r.ResortCountryID
AND ri.RelatedResortID = r.ResortID

ORDER BY ResortName

to bring back the all the records where r.ResortName LIKE '%' even if they are NOT in the T_Resort_Info, but if they are, i want the ri.ResortInfoID. So it can't be-

AND ri.RelatedResortID = r.ResortID

but not sure what to put. I want something like

AND IF ri.RelatedResortID = r.ResortID

i guess.

Thanks again for any help.

Ian

[Anon]

You need to do a LEFT JOIN on T_Resort_Info. I don't have a mysql DB to test multi-table query like this available, but the docs should explain it pretty well.

[Anon]

Thanks, do you mean -

SELECT r.ResortID,
s.ToSellAcceptPrice,
r.ResortName,
b.BedroomType,
w.WeekNumber,
c.ResortCountry,
ri.ResortInfoID

FROM T_ToSellInventory AS s,
T_Resort AS r,
T_Bedroom AS b,
T_Week AS w,
T_Country AS c,
T_Resort_Info AS ri

LEFT JOIN T_Resort_Info

ON ri.RelatedResortID = r.ResortID

WHERE r.ResortID = s.ToSellResortID
AND r.ResortName LIKE '%$resort_name%'
AND b.ID = s.ToSellBedroomID
AND w.ID = s.ToSellWeekID
AND c.ID = r.ResortCountryID

ORDER BY ResortName

I have tried this but mysql hangs for ages then times out. i have an index on ri.T_Resort_Info and without this left join it takes less than a second.

Am i doing something not quite right?

Cheers,

Ian