I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
From dbo.contact1 as a,
Select DISTINCT accountno
Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%'
) as b
Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
Select a.company, c.EMAILAddr
From dbo.contact1 as a
Select DISTINCT accountno DeviceAccountno
Where contact LIKE 'Product Inventory'
AND contsupref LIKE '%device%'
) as b
on a.accountno = b.DeviceAccountno
left outer join
Select DISTINCT accountno EMAILAccountno,
Where rtrim(contact) = 'Email Address'
AND contsupref LIKE '%@%'
) as c
on b.DeviceAccountno = c.EMAILAccountno
It's essentially inline views (or at least that's how I've always thought of sub-queries). If there can be multiple EMAIL addresses, then you might want to use "TOP 1" in a subordinate inline view to discard the extra ones.
That's crazy that the table contsupp contains such a disparity of data, without even a "type" column, and zero to many itterations.