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

Michelle,
Try :
LEFT JOIN tc_city ON jobs.jobno = tc_city.jobno
Cetin