passing information

What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)yup, derived table:

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
,t1.CONTSUPP
FROM dbo.CONTACT1 INNER JOIN
(
SELECT ACCOUNTNO, CONTSUPP
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
) As t1 ON t1.ACCOUNTNO = CONTACT1.ACCOUNTNO

You get the benefit of only generating the derived table once as well, as opposed to being evaluated once for each record when placed in the WHERE clause.|||Thank you, you pointed me in the right direction. There was one issue with the code you wrote because you cant group by ACCOUNTNO because the select has ACCOUNTNO and CONTACT. Anyway this is what the currently working code looks like. Thank you again, without your help I would not have been able to do this.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source,
t1.contact AS 'Device'
FROM dbo.CONTACT1
INNER JOIN (
SELECT accountno, contact
FROM dbo.CONTSUPP
WHERE accountno IN (
SELECT accountno
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY accountno
HAVING COUNT(*) <= 1
) AND (
contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
)
) AS t1
ON dbo.CONTACT1.accountno = t1.accountno