templemedia
Posts: 34
|
| Posted: 03/02/2005, 9:24 PM |
|
Hi All
I am having a problem with a project that I am working on using CCS with PHP+Mysql.
I have a table which has six tables joined to it in a similar fashion to the CCS Example pack many to many example.
I cannot get the grid to show the correct amount of records after a search. I either get 80 records instead of 1 or no records at all depending on the method for the datasource. I have tried setting this to both able and SQL. It is currently set to SQL and this returns no results. The query is shown below. I would be grateful if someone could give me some tips or advice to enable a solution.
Many thanks
Richard
SELECT *
FROM counties, ((((((main_data INNER JOIN countries ON
countries.ID = main_data.Country) LEFT JOIN main_data_facilities ON
main_data_facilities.RecNo = main_data.RecNo) LEFT JOIN main_data_meals ON
main_data_meals.RecNo = main_data.RecNo) LEFT JOIN main_data_non_smoking ON
main_data_non_smoking.RecNo = main_data.RecNo) LEFT JOIN main_data_payment_types ON
main_data_payment_types.RecNo = main_data.RecNo) LEFT JOIN main_data_pets_welcome ON
main_data_pets_welcome.RecNo = main_data.RecNo) LEFT JOIN main_data_special_dietary_requirements ON
main_data_special_dietary_requirements.RecNo = main_data.RecNo
WHERE main_data.County = {s_Counties}
AND main_data.Country = {s_Countries}
AND main_data_facilities.ID = {ListBoxFacilities}
AND main_data_meals.ID = {ListBox_MealsAvailable}
AND main_data_non_smoking.ID = {ListBox_Non_Smoking}
AND Name LIKE '%{s_Name}%'
AND ( main_data.Display_In_Search = 1 )
AND ( main_data.Expiry_Date >= CurDate() )
|
 |
 |
klwillis
Posts: 428
|
| Posted: 03/03/2005, 2:57 PM |
|
Now that's a query! 
My suggestion is to start with a simpler SQL join and then
work your way through the LEFT JOINS.
_________________
Kevin Willis, VP/CIO
HealthCare Information Technology Specialist
http://www.nexushealthcare.com
"Fast - Convenient - Quality-Care"
Medical Software Consulting Services
Email : klwillis@nexushealthcare.com
Skype : klwillis2006 |
|
|
|
|
