File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to write nested Query in java using Query Builder Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to write nested Query in java using Query Builder" Watch "How to write nested Query in java using Query Builder" New topic
Author

How to write nested Query in java using Query Builder

Dipakpsd Kumar
Greenhorn

Joined: Dec 08, 2011
Posts: 5
Hi All,

I need to write a nested Query in java using Query Builder. By using Query Builder we can Join tables but I don't know how to join nested Query. So Please help me. I am writing the Query here, If you have any idea then reply soon it's urgent.

SELECT QT_QUOTE.GROUP_ID AS Group_id, count(QT_QUOTE.QUOTE_ID) AS Quotes, GR_GROUP.NAME AS CaseName,HPR_RN_GROUP_EXT.PLAN_TYPE as 'Plan Type', GR_GROUP.XREF AS GroupXref,
CB_CONTACT.last_NAME+', '+CB_CONTACT.FIRST_NAME AS OpptDriver,a11.brokername
FROM
dbo.QT_QUOTE QT_QUOTE
INNER JOIN dbo.GR_GROUP GR_GROUP ON QT_QUOTE.GROUP_ID = GR_GROUP.GROUP_ID
INNER JOIN dbo.RN_RENEWAL_QUOTE RN_RENEWAL_QUOTE ON QT_QUOTE.QUOTE_ID = RN_RENEWAL_QUOTE.QUOTE_ID
INNER JOIN dbo.RN_RENEWAL_PERIOD RN_RENEWAL_PERIOD ON RN_RENEWAL_QUOTE.RENEWAL_PERIOD_ID = RN_RENEWAL_PERIOD.RENEWAL_PERIOD_ID
LEFT OUTER JOIN dbo.RN_RENEWAL RN_RENEWAL ON RN_RENEWAL_QUOTE.RENEWAL_ID = RN_RENEWAL.RENEWAL_ID

inner JOIN dbo.OW_GROUP_RESOURCE OW_GROUP_RESOURCE ON GR_GROUP.GROUP_ID = OW_GROUP_RESOURCE.GROUP_ID
inner JOIN dbo.OW_RESOURCE_OWNER OW_RESOURCE_OWNER ON OW_GROUP_RESOURCE.RESOURCE_ID = OW_RESOURCE_OWNER.RESOURCE_ID
inner JOIN dbo.OW_OWNER_ROLE OW_OWNER_ROLE ON OW_RESOURCE_OWNER.OWNER_ROLE_ID = OW_OWNER_ROLE.OWNER_ROLE_ID
inner join dbo.OW_OWNER OW_OWNER ON OW_OWNER_ROLE.OWNER_ID = OW_OWNER.OWNER_ID and (OW_OWNER_ROLE.ROLE_KEY='internaluser')
inner JOIN dbo.CB_CONTACT CB_CONTACT ON OW_OWNER.CONTACT_ID = CB_CONTACT.CONTACT_ID

left outer join
(select a1.group_id, CB_CONTACT.FIRST_NAME+', '+CB_CONTACT.LAST_NAME as BrokerName
from GR_GROUP a1
inner join OW_GROUP_RESOURCE OW_GROUP_RESOURCE on a1.GROUP_ID=OW_GROUP_RESOURCE.GROUP_ID
inner join OW_RESOURCE_OWNER OW_RESOURCE_OWNER on OW_RESOURCE_OWNER.RESOURCE_ID=OW_GROUP_RESOURCE.RESOURCE_ID
inner join OW_OWNER_ROLE OW_OWNER_ROLE on OW_OWNER_ROLE.OWNER_ROLE_ID=OW_RESOURCE_OWNER.OWNER_ROLE_ID and OW_OWNER_ROLE.ROLE_KEY ='broker'
inner join OW_OWNER OW_OWNER on OW_OWNER.OWNER_ID=OW_OWNER_ROLE.OWNER_ID
inner join CB_CONTACT CB_CONTACT on CB_CONTACT.CONTACT_ID=OW_OWNER.CONTACT_ID
) a11 on GR_GROUP.GROUP_ID=a11.group_id

INNER JOIN dbo.HPR_RN_GROUP_EXT HPR_RN_GROUP_EXT ON QT_QUOTE.GROUP_ID = HPR_RN_GROUP_EXT.GROUP_ID
LEFT OUTER JOIN dbo.RN_QUOTE_DOCUMENT_GROUP RN_QUOTE_DOCUMENT_GROUP ON RN_RENEWAL_QUOTE.RENEWAL_QUOTE_ID = RN_QUOTE_DOCUMENT_GROUP.RENEWAL_QUOTE_ID

GROUP BY a11.BrokerName,HPR_RN_GROUP_EXT.PLAN_TYPE, OW_OWNER_ROLE.ROLE_KEY, QT_QUOTE.GROUP_ID, GR_GROUP.NAME, GR_GROUP.XREF, CB_CONTACT.FIRST_NAME,CB_CONTACT.last_NAME

order by QT_QUOTE.GROUP_ID


Thanks in Advanced.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19783
    
  20

Please CarefullyChooseOneForum. Your question has nothing to do with servlets.

Dipakpsd Kumar wrote:reply soon it's urgent.

Please EaseUp. There is no such thing as urgent around here.

As for your problem. Query Builder is very, very limited in what it can do. It also produces horrible messes that are not readable by anyone. I've stopped using it ages ago, and just type in everything by hand. I really suggest you do the same. It gives you full control of what you want, and allows you to do things like fully supported nested queries, unions etc.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Dipakpsd Kumar
Greenhorn

Joined: Dec 08, 2011
Posts: 5
Rob Spoor wrote:Please CarefullyChooseOneForum. Your question has nothing to do with servlets.

Dipakpsd Kumar wrote:reply soon it's urgent.

Please EaseUp. There is no such thing as urgent around here.

As for your problem. Query Builder is very, very limited in what it can do. It also produces horrible messes that are not readable by anyone. I've stopped using it ages ago, and just type in everything by hand. I really suggest you do the same. It gives you full control of what you want, and allows you to do things like fully supported nested queries, unions etc.



Mainly, I wanted to know that with the help of Query builder in java we can join table references but how to join to Query, for e.g.

public TableRef abcRef = new TableRef() {
protected Table makeTable() {
return query.join(ABC).on(defRef.get(), _DEF.ID,
_ABC.ID);
}
};
here I'm joining table ABC but join subquery?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to write nested Query in java using Query Builder