distinct with multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

distinct with multiple tables

Author

Topic

neon20
Starting Member

2 Posts

Posted - 2007-04-04 : 06:46:41

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 06:52:43

1. I don't see need to use D-Sql here
2. Use JOIN clause instead of adding join conditions in WHERE clause. It is more readable and less prone to errors
3. Post Table structures, sample data and expected output when posting any question.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 07:11:01

4 Dont select all columns from all table instead select only required columns
5 Make use of stored procedure with parameter than using concatenated sql statements

Madhivanan

Failing to plan is Planning to fail

neon20
Starting Member

2 Posts

Posted - 2007-04-04 : 09:52:27

hi

thank you for the reply. i tried left join. 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

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-04 : 11:01:51