• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Join

 
Kalpesh Rawal
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

Have you executed the query in the database..?


Regards
saranga
 
Kalpesh Rawal
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic