quote:

---

Originally posted by CheesePlant

Hello,

I am very new to SQL (first day!) and I have a problem:


Works but doesn't include consupp.address1 only fields.

Select c1.Accountno, c1.Company, c1.City, c1.Address1, c1.State, c1.Zip, cs.ADDRESS2 as Primary_Contact, cs.CONTSUPREF as Email_Address, cs.Mergecodes as Email_Merge, c1.Mergecodes as Mail_Merge, cs.zip
From contsupp as cs, contact1 as c1
Where (cs.rectype = 'P' and cs.contact = 'E-mail Address') and c1.key1 = 'CUSTOMER' and substring(cs.zip,2,1)='1' and c1.accountno=cs.accountno

Works but no email shown:
SELECT * FROM CONTSUPP INNER JOIN CONTACT1 ON CONTSUPP.ACCOUNTNO=CONTACT1.ACCOUNTNO WHERE (CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'Enquiry' AND CONTSUPP.Address1 = 'test text')

------

How can I get the 2nd paragraph to display the email address too?

The 1st paragraph shows all the customers results fine but I need to only show ones where consupp.address1 = "test text"?

I'm not bother which code works just as long as it shows the same result :)

Can anyone please help me?

---

SELECT cs2.*, c1.*
FROM CONTSUPP cs
	JOIN CONTACT1 c1
		ON cs.ACCOUNTNO=c1.ACCOUNTNO 
		and	(	c1.zip like 'BA1%'
			OR	c1.zip like 'BA10%'
			OR	c1.zip like 'BA11%'
			)
		and	(	key4 = 'entry1' 
			OR	c1.key4 like '%entry2%' 
			OR	c1.key4 = 'entry3' 
			OR	c1.key4 = 'entry4'
			)
	join CONTSUPP cs2
		ON cs.ACCOUNTNO=cs2.ACCOUNTNO 
		and cs2.contact = 'E-mail Address'
		and cs2.rectype = 'P'
WHERE cs.RECTYPE = 'P' 
AND cs.CONTACT = 'Enquiry' 
AND cs.Address1 = 'test text'