I'm trying to get a filtered set of jobs with the customer's company and the job's address. The user can filter on various tables, so I decided to run the filters on each table individually to avoid having such huge temp cursors. This works except one problem. If there is a job that doesn't have any addresses, but the user isn't filtering on the address anyway, I still want to include it. But that doesn't work because the final query doesn't know the difference between a job that has no addresses period and a job that has no addresses that matched the filter.

I simplified the code below as much as I could and still get across what I'm trying to do. Is there a better way to work that last query that will take into account the above situation?

Thanks,

Michelle

LPARAMETERS tcCompany, tcCity

*-- Determine the customer filter
IF NOT EMPTY(tcCompany)
  lcCustFilt = "company = " + tcCompany
ELSE
  lcCustFilt = ".T."
ENDIF

*-- Get a cursor with all clients that fit the filter
SELECT custno, company ;
FROM customers ;
INTO CURSOR tc_custs NOFILTER ;
WHERE lcCustFilt

*-- Determine the address filter
IF NOT EMPTY(tcCity)
  lcCityFilt = "city = " + tcCity
ELSE
  lcCityFilt = ".T."
ENDIF

*-- Get a cursor with all addresses that fit the filter
SELECT city, state, jobno ;
FROM addrs ;
INTO CURSOR tc_city NOFILTER ;
WHERE lcCityFilt

*-- Join them with the main table
SELECT jobno, custno, company, city, state ;
FROM jobs ;
  INNER JOIN tc_custs ON jobs.custno = tc_custs.custno ;
  INNER JOIN tc_city ON jobs.jobno = tc_city.jobno