View Full Version : distinct from multiple tables

04-04-2007, 02:19 PM
hi friends

i have a query like this

query1="select co.*, ct.* from contacts co, enquiries en, contactPersonType ct where co.co_contactId=en.en_contactId and co.co_contactType=ct.ct_contactTypeId"

i want to display only the contact persons details who made enquires.
this gives me duplicate records. im not displaying enquiry details. how can i use distinct with this?

i know i get duplicates because i use *
i can use distinct if i use individual colums in the query instead of *.
as i have too many fields to select and the query itself is lengthy i use * instead.

all the ID fields are numeric and both the contacts and enquiries tables have atleast one memo field.

i used left join in this way

query1="select co.*, ct.*, cn.* from contacts co left join enquiries en on co.co_contactId=en.en_contactId left join contactPersonType ct on co.co_contactType=ct.ct_contactTypeId left join country cn on co.co_country=cn.ct_countryId"

and getting error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'co.co_ContactID = en.en_contactID LEFT JOIN contactPersonType ct on co.co_ContactType = ct.ct_contactTypeID'.

kindly help

thank you