Need to take this query a little further

[twiSSt]

Hi all,
I'm currently running this query

 select c.contactID, c.companyID, c.companyName, c.prefix, c. firstName, c. middleName, c.lastName, c.suffix,c.jobTitle, c.phy_address1, c.phy_address2, c.phy_address3, c.phy_city, c.phy_state, c.phy_zipCode, c.phy_country, c. mlg_address1, c.phone, c.referralID, c.statusId, cf.KEYCODE, ch.datecreated, ch.userid
from contacts c
inner join clientfields cf on c.contactID = cf.contactID
left join callHistory ch on c.contactID = ch.contactID
HAVING (ch.dateCreated =
(SELECT DISTINCT MIN( ch.dateCreated) FROM callHistory ch WHERE ch.contactID = c.contactID))

That produces all of the records with a dateCreated in the table callHistory. I should clarify for each attempt that is made to call a contact there is one record for it in the callHistory. So one contact can have many callhistoryRecords.

The query above gives me all with the earliest dateCreated, which is what I set out to do. Now I need it to give me that result set plus any record that does not yet have a callHistory dateCreated value.

I have to run this once a day to match against a file provided to us from the client.

Twist

===========================================
Everything will be OK in the end.
If it's not OK, then it's not the end

 

[r937]

change HAVING to AND, clean up the aliases in the subquery, remove the DISTINCT ...

select ...
  from contacts c
inner 
  join clientfields cf 
    on c.contactID = cf.contactID
left outer
  join callHistory ch 
    on c.contactID = ch.contactID
   and ch.dateCreated =
       ( select min(dateCreated) 
           from callHistory  
          where contactID = c.contactID )

r937.com | rudy.ca

 

[twiSSt]

Thanks that works. the only issue that i face now is duplicates. In other words, if someone was called twice then i get 2 records for that person.

Twist

===========================================
Everything will be OK in the end.
If it's not OK, then it's not the end