Looking closely, you've got 3 distinct queries, joined by two UNION statements.
Since each of the 3 distinct queries actually includes the CONTACT1 table, you could add the following to EACH of the 3 distinct queries and it should work well:
LEFT JOIN CONTACT2 on (CONTACT1.accountno = CONTACT2.accountno)
I'm simply sharing in this manner, rather than copy-n-pasting your entire query with my few additions.
I'd suggest you put this on the next line AFTER the "from contact1" line in each case (for simplicity and consistency sake). Once you've done this, you can THEN make reference to CONTACT2.<fieldnames> as you wish. Just make sure you account for the CONTACT2 fields (or provide appropriate spacing/placeholders) in EACH of the 3 queries, as needed.
I use a LEFT JOIN (vs. INNER JOIN) just in case any records do NOT have a corresponding CONTACT2 record, the other records will still be included. If I used INNER JOIN instead, and any CONTACT1 records did NOT have the associated CONTACT2 record, then the CONTACT1 record (and associated email addresses, etc.) would be excluded from the query results altogether. In my experience, this (excluding a record due simply to there NOT being a CONTACT2 record) is SELDOM what is desired. So, using LEFT JOIN, as I have, is the safest approach.
Hope this feedback helps. Please let me know if I need to clarify anything.
You can do it like this
select * from CONTACT1
inner join CONTACT2
(select isnull(CONTSUPREF,'')+isnull(address1,'') as 'E-mail Address',ACCOUNTNO from CONTSUPP where RECTYPE='P' and U_CONTACT='E-MAIL ADDRESS') CS
Replace the * with your needed fields from CONTACT1 / CONTACT2
Thank you both for your suggestions. I will try both approaches.
Shaul Bel-- Will your much simplified query return both primary and additional contacts with email addresses?
In contsupp table you have 3 types of emails.
Emails that are linked to an additional contact (when you double click on additional contact you can see the email that is linked to that contact)
emails that are not linked to an additional contact.
my query gets all the emails but you don't know if they are linked to specific additional contact.
you can add to the sub query the field ADDRESS2 and you will get the name if you will also add LINKACCT you will be able to know if it's linked to additional contact.
The question is what's your goal.
Thank you for your follow-up information, Shaul Bel. My goal was to get a list of linked email addresses for primary and secondary contacts with other pertinent information from contact 1 and contact 2 table. John Neighbors' addition to my original query seems to give me exactly what I am looking for. I see the value of yours that it returns every email address whether linked or not. Your query gets me to our limit of 40000 records while John's and my query returns about 14500.
This works great for what I want but I would like to only select Where Contact.key2 like '%Customer%'
Where would I add the where clause to give me contacts who have "Customer" in the Key2 field?
I tried it after the from contact1 but it was not happy?
I think I got it. I put the Where clause after the Inner Joins and it seems to have worked!