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?

thank you
neon

hmmm. I don't know how your database is laid out but I would do something similiar to this:

SELECT co.*, ct.* FROM enquiries en
LEFT JOIN contacts co on co.co_ContactID = en.en_contactID
LEFT JOIN contactPersonType ct on co.co_ContactType = ct.ct_contactTypeID

The problem with your duplicates is probably because you are selecting * and rather not particular fields from anyone table and, it seems to me, that you should have a WHERE clause determing which records in the enquiries table you want to deal with.

Anyway, try that and let me know.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

select DISTINCT co.*, ct.* from contacts ....
???

That probably wont simply because they are selecting everything and, assuming there is a pk value in the tables, each row will be Distinct.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

hi dparsons

thank you for the reply. i tried left join as you said. it doesnt work.

giving 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'.

--------


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.

here is my actual query

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"


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

thanks you
neon

You may have flawed logic on what your requesting. If the same person makes multiple enquires do you want to see them once or multiple times? My thoughts is you should see them multiple times. Also if multiple people make the same enquires should you see them multiple times? My thoughts here are also yes.

hi
i could solve this problem by installing Jet 4.0 Database Engine Service Pack 8 (SP8)