multiple inner joins 1

[Richey]

i have the following sql statement which I'd like to add another inner join to. I have a table called cts_enquiry_services which has two columns I'm interested in, EnquiryTypeID and ServiceID. ServiceID links back to the
ServiceID in cts_services, so i want to add an inner join for cts_enquiry_services ON serviceid=serviceid and also include a where statment for Where EnquiryTypeID (from cts_enquiry_services) = 17

thanks
kim

SELECT ServiceID, DeptDesc, ServiceDesc as ServiceDesc,TargetDays,VOID,DEPARTMENT
FROM cts_services INNER JOIN cts_departments ON department=deptid WHERE minaccess <= 50 and serviceid IN (SELECT service FROM cts_service_locations WHERE location='ICT') 
UNION
SELECT ServiceID, DeptDesc,ServiceDesc as ServiceDesc,TargetDays,VOID,DEPARTMENT FROM cts_services INNER JOIN cts_departments ON department=deptid WHERE minaccess <=50 and serviceid NOT IN (SELECT service FROM cts_service_locations WHERE location='ICT')

 

[r937]

either you made an error in cutting and pasting your query here, or else if that's really your query, you don't need a UNION and you don't need to look at the cts_service_locations table at all

here's your query, reformatted so that it's easier to see why the two selects in the union are mutually exclusive --

SELECT ServiceID
     , DeptDesc
     , ServiceDesc as ServiceDesc
     , TargetDays
     , VOID
     , DEPARTMENT
  FROM cts_services 
INNER 
  JOIN cts_departments 
    ON department=deptid 
 WHERE minaccess <= 50 
   and serviceid 
    [COLOR=red]IN[/color] (SELECT service 
          FROM cts_service_locations 
         WHERE location='ICT') 
UNION
SELECT ServiceID
     , DeptDesc
     , ServiceDesc as ServiceDesc
     , TargetDays
     , VOID
     , DEPARTMENT 
  FROM cts_services 
INNER 
  JOIN cts_departments 
    ON department=deptid 
 WHERE minaccess <=50 
   and serviceid 
[COLOR=red]NOT IN[/color] (SELECT service 
          FROM cts_service_locations 
         WHERE location='ICT')

now add your new table --

SELECT cts_services.ServiceID
     , DeptDesc
     , ServiceDesc as ServiceDesc
     , TargetDays
     , VOID
     , DEPARTMENT
     , EnquiryTypeID
  FROM cts_services 
INNER 
  JOIN cts_departments 
    ON department=deptid 
INNER 
  JOIN cts_enquiry_services 
    ON cts_services.ServiceID
     = cts_enquiry_services.ServiceID
 WHERE minaccess <= 50

r937.com | rudy.ca