GoldMine SQL Query (Stuck)

Can someone help me fine tune this query, It is taking too long so I must be doing something wrong.


select
      contact1.company,
      contact1.contact,
      contsupp.address2 as EM_Contact,
      contsupp.contsupref,
      contsupp.zip
      from contsupp join contact1
      on contact1.accountno=contsupp.accountno and
      contsupp.contact = 'E-mail Address'
      where
      contact1.accountno in
      (select * from
      contact1,
      conthist,
      contact2,
      contsupp
      where contact1.accountno=conthist.accountno and
      contact1.accountno = contact2.accountno and
      conthist.ref like
      '%Seminar Approaching%' and
      contact2.umarket = 'New York' or contact2.umarket = 'New Jersey')
ratzephyrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jdlambert1Connect With a Mentor Commented:
Ah, add the "Distinct" keyword:

SELECT Distinct c1.company, c1.contact, cs.address2 as EM_Contact, cs.contsupref, cs.zip
FROM contsupp cs
 JOIN contact1 c1 ON c1.accountno = cs.accountno
 JOIN contact2 c2 ON c1.accountno = c2.accountno
 JOIN conthist ch ON c1.accountno=ch.accountno
WHERE cs.contact = 'E-mail Address'
 AND ch.ref like '%Seminar Approaching%'
 AND c2.umarket IN ('New York','New Jersey')
0
 
jdlambert1Commented:
Try this:

SELECT c1.company, c1.contact, cs.address2 as EM_Contact, cs.contsupref, cs.zip
FROM contsupp cs
 JOIN contact1 c1 ON c1.accountno = cs.accountno
 JOIN contact2 c2 ON c1.accountno = c2.accountno
 JOIN conthist ch ON c1.accountno=ch.accountno
WHERE cs.contact = 'E-mail Address'
 AND ch.ref like '%Seminar Approaching%'
 AND c2.umarket IN ('New York','New Jersey')

Indexes on contsupp(contact) and on contact2(umarket) will help performance, as well as on all accountno columns, if these don't already have indexes.
0
 
ratzephyrAuthor Commented:
jdlambert1

Your query is pulling everything four times
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.