Join

[Kalpesh Rawal]

HI.
if 2 tables are not related to each other then how can i join these 2 tables.I am using HQL.

Thanks

[saranga rao]

Hi,

if table A and table B

then

List lst = (Query)createQuery("from A as a B as b where a.id= b.id").list();

this is equi join

or

left join use the same query like this in another way

you can also use createCrietria()with different stratgey

[Kalpesh Rawal]

but this will give me only those records where a.id=b.id. i want all the records of table a and for which id of a,b.flag=1 i want name from b table and for those id where b.flag=0 or b.flag is nthng,i dont want name but i also want those records of table a.

[saranga rao]

Hi


can You be more specific (more explanation the things)

or give the table structure of the two table and give what you require........

[Kalpesh Rawal]

i have a grid in which data are coming by forming this query

HSQL query = select InvTxn.ivStr2,InvTxn.ivAttdtls,trunc(current_date()-InvTxn.ivDuedate),InvTxn.cstHdr.csName,InvTxn.comp_id.ivId,InvTxn.comp_id.ivDate,InvTxn.ivDuedate,InvTxn.ivTlamt,InvTxn.accrTxn.arOutamt,InvTxn.cstHdr.csPayr,CmtsLog.cmAction,CmtsLog.cmText,InvTxn.ivCurr,StsLog.stsHdr.stName,InvTxn.cstHdr.csId from InvTxn as InvTxn LEFT JOIN InvTxn.stsLogs as StsLog LEFT JOIN InvTxn.cmtsLogs as CmtsLog where InvTxn.enttyHdr.enId IN (4) and InvTxn.ivClosed = 0 order by InvTxn.cstHdr.csId,InvTxn.ivDuedate

I m firing this query on my InvTxn Table.now when i select(using check box) a invoice from grid and attach a file then that ivid and ivdate and url saves into my another table called Attmts_log,also in my attachment panel i have a check box if it selects at the time of attachment then flag will be saved as 1 for that invoice id in attmts_log table and if it deselects then it saves as 0 in attmts_log or if i wont attache any file for any invoice flag vaue is neither 0 nor 1.

Now i want all the records that are coming from my above query and if for any invoice,flag finds 1 i want the url of the attchaed file.if it doesn't find 1 or if it finds 0 or nothing..i dont want the url but the entire record from above query should be appear..

If further confussion please let me know.


Thanks

[saranga rao]

HSQL query = select InvTxn.ivStr2,
InvTxn.ivAttdtls,
trunc(current_date()-InvTxn.ivDuedate),
InvTxn.cstHdr.csName,
InvTxn.comp_id.ivId,
InvTxn.comp_id.ivDate,
InvTxn.ivDuedate,
InvTxn.ivTlamt,
InvTxn.accrTxn.arOutamt,
InvTxn.cstHdr.csPayr,
CmtsLog.cmAction,
CmtsLog.cmText,
InvTxn.ivCurr,
StsLog.stsHdr.stName,
InvTxn.cstHdr.csId
from InvTxn as InvTxn
LEFT JOIN InvTxn.stsLogs as StsLog
LEFT JOIN InvTxn.cmtsLogs as CmtsLog
where
InvTxn.enttyHdr.enId IN (4)
and InvTxn.ivClosed = 0
order by
InvTxn.cstHdr.csId,
InvTxn.ivDuedate



hi actually have specified 2 tables but here giving an alias name.............right...........

[saranga rao]

Hi

Have you executed the query in the database..?


Regards
saranga

[Kalpesh Rawal]

Hi..

Yes i have 1 single table (InvTxn) here.and in that table i have the mapping for StsLog,EnttyHdr and CmtsLog..But i dont have any kind of mapping of attmts_log table..i wantto join this table.

And yes i have excutred this query.i have pasted this query from my Weblogic console..

Thanks.