SQL JOIN help

[Ivan Smahin]

Hello Sean,

Monday, May 30, 2005, 7:28:17 AM, you wrote:

SW> I have 4 tables - customers, locations, tasks and taskTypes - that I need
SW> to query. I have a taskTypeName that I need to filter the results on. The
SW> following works, but doesn't filter on the taskTypes.taskTypeName

SW> SELECT
SW> tblCustomers.custLName, tblCustomers.custFName, tblLocations.locRoad,
SW> tblTasks.taskArea, tblTasks.taskOrderDate, tblTasks.taskReadyDate
SW> FROM
SW> tblCustomers, tblLocations, tblTasks
SW> WHERE
SW> tblTasks.taskLocID = tblLocations.locID AND tblLocations.locCustId =
SW> tblCustomers.custID AND IsNull(tblTasks.taskCompleteDate)

SW> How do I join/sub-select the tasks table on the taskType table so that if I
SW> have a specific taskTypeName (for example "ploughing") I can select records
SW> of that type AND maintain the joins I already have. The tasks table has a
SW> taskTTypID foreign key field that points to the taskType table. I suspect
SW> my existing query could be re-worded as a JOIN, but I'm not sure what that
SW> might look like and even why/whether a JOIN might be more efficient than
SW> the existing WHERE based on foreign keys

As far as I get it
You could try something like this:

SELECT *
FROM
    tblCustomers, tblLocations, tblTasks, taskTypes
WHERE
 tblTasks.taskTypeName = taskTypes.taskTypeName
 
 AND tblTasks.taskLocID = tblLocations.locID
 AND tblLocations.locCustId = tblCustomers.custID
 AND IsNull(tblTasks.taskCompleteDate)

 AND tblTasks.taskTypeName = 'ploughing'


 or another approach (based on subquery):
 
SELECT *
FROM
    tblCustomers, tblLocations, tblTasks,
WHERE
 tblTasks.taskLocID = tblLocations.locID
 AND tblLocations.locCustId = tblCustomers.custID
 AND IsNull(tblTasks.taskCompleteDate)

 AND  tblTasks.taskTypeName IN ( select taskTypeName from taskTypes
 where ......)
 
-- 
Best regards,
 Ivan                            mailto:IvanSmahin at public.kherson.ua