March 10, 2005 at 5:10 am
Hi all,
What I want is a single SQL Statement, if possible,
Table bellow is a result of 3 differnet select, Now, i want to join to 3 other tables. if EMPLOYEEID is not null then link it to E__EMPLOYEE, if SUPPLIERID is not null then to S_ORGANISATION and so on..How do i do it ? Help pls
TENANCYID SUPPLIERID EMPLOYEEID TABLENAME
----------- ----------- ----------- --------------
NULL NULL 34 E__EMPLOYEE
NULL NULL 97 E__EMPLOYEE
NULL NULL 105 E__EMPLOYEE
NULL 3 NULL S_ORGANISATION
NULL 31 NULL S_ORGANISATION
NULL 147 NULL S_ORGANISATION
NULL 330 NULL S_ORGANISATION
NULL 395 NULL S_ORGANISATION
270393 NULL NULL C_TENANCY
866385 NULL NULL C_TENANCY
970469 NULL NULL C_TENANCY
970597 NULL NULL C_TENANCY
March 10, 2005 at 6:33 am
Not sure if this is what you want. It all depends on what you want to do with the data from the additonal tables.
LEFT OUTER JOIN E__EMPLOYEE e
ON [result].EMPLOYEEID IS NOT NULL
AND e.EMPLOYEEID = [result].EMPLOYEEID
LEFT OUTER JOIN S_ORGANISATION o
ON [result].SUPPLIERID IS NOT NULL
AND o.SUPPLIERID = [result].SUPPLIERID
LEFT OUTER JOIN C_TENANCY t
ON [result].TENANCYID IS NOT NULL
AND t.TENANCYID = [result].TENANCYID
This will return the columns from each table and will contain nulls where the ID is null or the matching rows
You can then select what data you require using eith CASE or COALESCE
Really need more info.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply