incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alain Becam <Alain.Be...@embl.de>
Subject Unexpected behaviour while using notIn
Date Thu, 29 Sep 2011 11:38:26 GMT
Hello all,
     I have been trying to write a simple query with a notIn:


			
DBCommand cmdFindPubForPerson = dbPerson.createCommand();
			cmdFindPubForPerson.select(publication4Group.C_PUBLICATION_ID);		 
cmdFindPubForPerson.where(publication4Group.C_GROUP_ID.is(group_Id));
	
DBQuery queryForAssociatedPub = new DBQuery(cmdFindCollForPerson);
			
	
DBCommand cmdFindPub = dbPerson.createCommand();
	
cmdFindPub.selectDistinct();
cmdFindPub.select(publicationsFromDb.getColumns());	 
cmdFindPub.where(publicationsFromDb.C_ID.notIn(queryForAssociatedColl));

And was expecting this query:

SELECT DISTINCT t11.CREATEDBY, ...
FROM PUBLICATIONS t11
WHERE t11.ID NOT IN ((SELECT DISTINCT t13.PUBLICATION_ID
FROM PUBLICATION4GROUP t13
WHERE t13.GROUP_ID=27))

but got that:

SELECT DISTINCT t11.CREATEDBY, ...
FROM PUBLICATIONS t11, PUBLICATION4GROUP t13
WHERE t11.ID NOT IN ((SELECT DISTINCT t13.PUBLICATION_ID
FROM PUBLICATION4GROUP t13
WHERE t13.GROUP_ID=27))

which is wrong and pretty slow (well should be).

Is there a reason I get the PUBLICATION4GROUP table in the main query 
from? Might be a very stupid question, I wonder if I am not missing 
something pretty simple here :)

//Alain

Mime
View raw message