Re: [eclipselink-users] using IN with a subquery

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Derek,

  What version of EclipseLink are you using?

  How are your entities mapped? (particularly the mappings that relate them)

-Tom

Derek Knapp wrote:
> I have 3 entities, User, Account, and Task
>
> this is my query
>
> select t from Task t where t.user = :user or t.account in (select a from 
> User u join u.accounts a where u = :user)
>
> here is the SQL generated
>
> SELECT t0.`id`, t0.`complete_date`, t0.`create_date`, t0.`due_date`, 
> t0.`task_status`, t0.`uuid`, t0.`userid`, t0.`contactid`, t0.`noteid`, 
> t0.`subuserid` FROM `task` t0, `users` t1 WHERE (((t0.`subuserid` = ?) 
> OR  IN (SELECT DISTINCT t2.`userid`, t2.`adminlevel`, t2.`cellno`, 
> t2.`city`, t2.`cityid`, t2.`country`, t2.`disclaimer`, t2.`email`, 
> t2.`firstname`, t2.`lastname`, t2.`managerid`, t2.`office`, 
> t2.`officeid`, t2.`password`, t2.`phoneno`, t2.`region`, t2.`regionid`, 
> t2.`signatureline`, t2.`signaturestatus`, t2.`team`, t2.`teamid`, 
> t2.`title`, t2.`companyid` FROM `subuseraccess` t4, `subusers` t3, 
> `users` t2 WHERE ((? = t3.`subuserid`) AND ((t4.`subuserid` = 
> t3.`subuserid`) AND (t2.`userid` = t4.`userid`))))) AND (t1.`userid` = 
> t0.`userid`))
>     bind => [6, 6]
>
> the JPA query seems to be fine, but the SQL it generates is not.. here 
> is the mysql error
>
> You have an error in your SQL syntax; check the manual that corresponds 
> to your MySQL server version for the right syntax to use near 'IN 
> (SELECT DISTINCT t2.`userid`, t2.`adminlevel`, t2.`cellno`, t2.`city`, 
> t2.`ci' at line 1
>
>
>
> also, why does it include the users table (t1)..  this is what I would 
> HOPE it generated
>
> SELECT t0.`id`, t0.`complete_date`, t0.`create_date`, t0.`due_date`, 
> t0.`task_status`, t0.`uuid`, t0.`userid`, t0.`contactid`, t0.`noteid`, 
> t0.`subuserid` FROM `task` t0 WHERE (((t0.`subuserid` = 6) OR 
> t0.`userid` IN (SELECT DISTINCT t2.`userid` FROM `subuseraccess` t4, 
> `subusers` t3, `users` t2 WHERE ((6 = t3.`subuserid`) AND 
> ((t4.`subuserid` = t3.`subuserid`) AND (t2.`userid` = t4.`userid`))))))
>     bind => [6, 6]
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users