joining

[dennis bekkering]

Hello,

I have class Mail with some collections that return contacts.

public class Mail
{
 private Collection attachementList = new ArrayList();
 private Collection bccList = new ArrayList();
 private Collection ccList = new ArrayList();
 private Collection toList = new ArrayList();
........
 }

descriptor :

  <collection-descriptor name="toList"
element-class-ref="nl.salesmakers.model.Contact" auto-retrieve="true"
auto-update="true" auto-delete="false" indirection-table="MailTo2Contact">
    <fk-pointing-to-this-class column="mailid"/>
    <fk-pointing-to-element-class column="contactId"/>
  </collection-descriptor>
  <collection-descriptor name="ccList"
element-class-ref="nl.salesmakers.model.Contact" auto-retrieve="true"
auto-update="true" auto-delete="false" indirection-table="MailCc2Contact">
    <fk-pointing-to-this-class column="mailid"/>
    <fk-pointing-to-element-class column="contactId"/>
  </collection-descriptor>
  <collection-descriptor name="bccList"
element-class-ref="nl.salesmakers.model.Contact" auto-retrieve="true"
auto-update="true" auto-delete="false" indirection-table="MailBcc2Contact">
    <fk-pointing-to-this-class column="mailid"/>
    <fk-pointing-to-element-class column="contactId"/>
  </collection-descriptor>


My sql query for getting all mail messages send to a certain contact :

" SELECT distinct m.* " +
 " FROM " +
      " Mail m , " +
      " Contact c , " +
      " MailTo2Contact mtc ," +
      " MailCc2Contact mcc ," +
      " MailBcc2Contact mbc " +
 " WHERE " +
       " (( m.id = mtc.mailid" +
           " AND" +
       " c.id = mtc.contactid )" +
  " OR " +
       " (m.id = mcc.mailid" +
           " AND" +
       " c.id = mcc.contactid )" +
  " OR " +
       " (m.id = mbc.mailid" +
           " AND" +
       " mbc.contactid = c.id   ))" +
  " AND " +
      " c.id = " + contact.getId() +
  " AND " +
      " m.flag = 'sent'" +
  " order by m.date desc"    +
  "";

I offcourse would like to use the pb API but dont know how. Any hints? I
cannot use select in , since mysql 4.0 does not support them and 4.1 is not
in production.

regards,
Dennis

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]