Hi please read through and be patient as I am a bit of a
novice.I am trying to create what I think should be a
simple query, but I seem to be making a pigs ear of it.

Here is the query;

select contact1.company, (contsupp.contsupref where
contsupp.contact like 'E-Mail Address' and contsupp.zip
like '0111' )as Email , contact2.userdef10 from contact1,
contact2, contsupp
where contact1.accountno = contact2.accountno and
contact1.accountno = contsupp.accountno and contact1.key5
like 'Nuts'

The query involves getting data from 3 tables;
1. Contact1
2. Contact2
3. Contsupp

The primary key is called accountno

The criteria for the query is that the word "Nuts" exists
in contact1.key5
And I want to diplay 3 things;
1. The Company Name
2. The value in contact2.userdef10
3. The Primary Email address
The problem is the contsupp table is a one to many
relationship which means that to display the primary
email address I need to first ascertain that the row is
in fact an E-Mail Address (as you can see this data is
stored in a field called contsupp.contact
(...contsupp.contact like 'E-Mail Address' ....)Secondly
the thing that denotes a primary email address is the

You might be able to get this done using a CASE in the SELECT list, but not
sure since you have not provided sufficient information to test your
problem. Mind posting a repro? For details, refer to : www.aspfaq.com/5006

--
Anith