Hi all,

I want to use Intersection in my query.Can you please help me in writing the below query in LLBL.

(Select emp1  from Employees where empid = 5 and deptid=5) 
INTERSECT 
(Select emp1  from Employees where empid = 7 and deptid=9) 

The above query work fine in SQL. But is it possible to do the same in LLBL?

Thanks.

Intersects is not supported.

(Select emp1 from Employees where empid = 5 and deptid=5) INTERSECT (Select emp1 from Employees where empid = 7 and deptid=9)

I don't think the above is a real query, is it? In most cases you can rewrite the queries to avoid using Intersects.

The above is not a real query but I've to use exactly a query like that.

Is there any other way??

Other than using a database view or a stored procedure to implement the INTERSECT query, you may post your query here so we may try to help you re-formulate it to give the same results and avoid using the INTERSECTS.

(Select Entity1 from CompanyEntity
where Entity2=2 and CompanyID=3 and  IsRelation=1) 
 intersect 
(Select Entity2 from CompanyEntity
where Entity1=3 and CompanyID=3 and  IsRelation=1)

The above query is the exact query.

The first SELECT statement gives me some number For eg: 2,4,5,7,9

And the second SELECT statement also results in some numbers. For eg:1,2,5,7,8

And out of these numbers I want only the common ones like 2,5 ,7

The first and second statement involves only one table i.e. CompanyEntity

Can you help me reformulate??

SELECT Entity1 
FROM CompanyEntity
WHERE Entity2=2 
AND CompanyID=3 
AND IsRelation=1
AND Entity1 IN (SELECT Entity2 FROM CompanyEntity
WHERE Entity1=3 AND CompanyID=3 AND IsRelation=1)

I think the above query should give the same results. And you may use FieldCompareSetPredciate to implement the IN predicate.

Hi, Thanks for the query. As even i had started trying to write the query I got the same query as you wrote.

Select EntityTypeID1 from CompanyEntityTypeRelation 
where EntityTypeID1 in (Select EntityTypeID2 from CompanyEntityTypeRelation 
where EntityTypeID1=3 and CompanyID=3 and RelationTypeID=1 and IsDirectRelation=1)
and EntityTypeID2=2 and CompanyID=3 and RelationTypeID=1 and IsDirectRelation=1

And it works now. Anyways thanks a lot.