SQL statement issue

[LucyP]

I have two related tables in a database. One stores the names and descriptions of various email lists people can subscribe to, and the other stores the email addresses. So that the system is fully flexible, there is a separate row in the second table for every email address (containing listname and emailaddress).

What I need is a sql statement that will return all of the values from the listnames table, along with whether or not a particular email address is listed as belonging to that list. At present I'm having to do this with 2 sql staements:

SELECT listname FROM listnames

and

SELECT listname FROM emailaddresses WHERE emailaddress='test@test.com'

I'm then using a few lines of VB to marry the matching items up so that the html page can display a list of all the email lists with 'subscribed'/'not subscribed'

There must be an easier way to do it! (eg can I specify 'distinct' just for one item, rather than for whole rows?)

 

[tlbroadbent]

Use a LEFT JOIN query. In this case, join the listnamse table to a query that selects record from EmailAddresses based for a particular EmailAddress.

Select
l.Listname,
Case
When e.EMailAddress is null Then 'Not enrolled'
Else 'Enrolled'
End As EnrollStatus
From listnames As l
Left Join
(Select listname, emailaddress
From emailaddresses
Where EmailAddress='bob@email.com') As e
On l.listname=e.listname Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[LucyP]

wow - it works a treat, thanks.