dotnet 3.5 llblgen 2.6 sqlserver 2005

I have 3 tables: client, employer, and person. The tables have the following relationships: client.Id = employer.client_Id, client.Id = person.client_Id, employer.Id = person.employer_Id. Basically an employer belongs to a client and a person can belong to either a client or an employer. Thus person can belong to multiple employers due to client 1:n employer.

I want to do the following query in llblgen. It works in sqlserver. Can some one shows me how to?

select e.* from employer e join client c on c.id=e.client_id join person p on (e.id=p.employer_id or c.id=p.client_id) where p.id=12345

Thanks

BZ

Could your query be simplified to the following?

SELECT * FORM Bill
WHERE client_Id = 12345
OR employer_Id IN (SELECT Id FROM Employer WHERE client_id = 12345)

Sorry, the original post was not completely correct. I must have edited it after you post your reply. Could you please take another crack at this? Thanks.

You can use the CustomerFilter property of an EntityRelation and set the CustomFilterReplacesOnClause to false. But this will add the additional filter with an AND not an OR.

This leaves you with one option, you have to split the JOINs and split the filter condition. i.e. your query should be modified to lok like the following:

SELECT e.* FROM Employer e
JOIN Client c on e.Client_Id = c.Id
LEFT OUTER JOIN Person p on c.Id = p.Client_Id
LEFT OUTER JOIN Person p2 on e.Id = p2.Employer_Id
WHERE p.Id = 12345
OR p2.Id = 12345