select usrprof from Usrprofiles as usrprof, Compmsgs, Usrmsgs left join fetch usrprof.compdatas as comps left join fetch usrprof.usrmsgses umsgs left join fetch comps.compmsgses compmsgs where (usrprof.username='johndoe' and compmsgs.viewed=false and umsgs.viewed=false) order by comps.companyname asc, compmsgs.logdate desc, umsgs.logDate desc

umsgs.logDate desc

[5/3/08 17:43:19:188 CDT] 0000008d SystemOut     O Hibernate:
    select
        usrprofile0_.UID as UID164_0_,
        compdata4_.COMPANYID as COMPANYID134_1_,
        compmsgses6_.MSGNUM as MSGNUM137_2_,
        usrmsgses5_.USRMSGSID as USRMSGSID163_3_,
        usrprofile0_.CONTACTID as CONTACTID164_0_,
        usrprofile0_.ROLE as ROLE164_0_,
        usrprofile0_.FIRSTNAME as FIRSTNAME164_0_,
        usrprofile0_.LASTNAME as LASTNAME164_0_,
        usrprofile0_.DATECREATED as DATECREA6_164_0_,
        usrprofile0_.USERNAME as USERNAME164_0_,
        usrprofile0_.PASSWORD as PASSWORD164_0_,
        usrprofile0_.DESC as DESC164_0_,
        usrprofile0_.EMAIL as EMAIL164_0_,
        usrprofile0_.FIRST_CH_Q as FIRST11_164_0_,
        usrprofile0_.FIRST_CH_A as FIRST12_164_0_,
        usrprofile0_.SEC_CH_Q as SEC13_164_0_,
        usrprofile0_.SEC_CH_A as SEC14_164_0_,
        usrprofile0_.THIRD_CH_Q as THIRD15_164_0_,
        usrprofile0_.THIRD_CH_A as THIRD16_164_0_,
        usrprofile0_.ACTIVE as ACTIVE164_0_,
        usrprofile0_.EMAIL_SIGNATURE as EMAIL18_164_0_,
        usrprofile0_.EMAIL_ALERTS_OK as EMAIL19_164_0_,
        usrprofile0_.LAST_UPDATE as LAST20_164_0_,
        compdata4_.CONTACTID as CONTACTID134_1_,
        compdata4_.COMPANYNAME as COMPANYN3_134_1_,
        compdata4_.REM_BAL as REM4_134_1_,
        compdata4_.ACCESSCODE as ACCESSCODE134_1_,
        compdatas3_.UID as UID0__,
        compdatas3_.COMPANYID as COMPANYID0__,
        compmsgses6_.MSGTYPENUM as MSGTYPENUM137_2_,
        compmsgses6_.COMPANYID as COMPANYID137_2_,
        compmsgses6_.MSG as MSG137_2_,
        compmsgses6_.LOGDATE as LOGDATE137_2_,
        compmsgses6_.VIEWED as VIEWED137_2_,
        compmsgses6_.TIME_READ as TIME7_137_2_,
        compmsgses6_.TIME_VIEWED as TIME8_137_2_,
        compmsgses6_.SENT_BY as SENT9_137_2_,
        compmsgses6_.COMPANYID as COMPANYID1__,
        compmsgses6_.MSGNUM as MSGNUM1__,
        usrmsgses5_.MSGTYPENUM as MSGTYPENUM163_3_,
        usrmsgses5_.UID as UID163_3_,
        usrmsgses5_.MSG as MSG163_3_,
        usrmsgses5_.LOG_DATE as LOG5_163_3_,
        usrmsgses5_.VIEWED as VIEWED163_3_,
        usrmsgses5_.TIME_VIEWED as TIME7_163_3_,
        usrmsgses5_.SENT_BY as SENT8_163_3_,
        usrmsgses5_.UID as UID2__,
        usrmsgses5_.USRMSGSID as USRMSGSID2__
    from
        faweb.USRPROFILES usrprofile0_
    left outer join
        faweb.USRPROFILES_X_COMPDATA compdatas3_
            on usrprofile0_.UID=compdatas3_.UID
    left outer join
        FAWEB.COMPDATA compdata4_
            on compdatas3_.COMPANYID=compdata4_.COMPANYID
    left outer join
        faweb.COMPMSGS compmsgses6_
            on compdata4_.COMPANYID=compmsgses6_.COMPANYID
    left outer join
        faweb.USRMSGS usrmsgses5_
            on usrprofile0_.UID=usrmsgses5_.UID,
        faweb.COMPMSGS compmsgs1_,
        faweb.USRMSGS usrmsgs2_
    where
        usrprofile0_.USERNAME='johndoe'
        and compmsgses6_.VIEWED='F'
        and usrmsgses5_.VIEWED='F'
    order by
        compdata4_.COMPANYNAME asc,
        compmsgses6_.LOGDATE desc,
        usrmsgses5_.LOG_DATE desc