Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 4th, 2007, 06:13 AM
Authorized User
 
Join Date: Mar 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default distinct from multiple tables

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

 
Old April 4th, 2007, 06:34 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old April 4th, 2007, 07:46 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old April 4th, 2007, 07:54 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old April 4th, 2007, 08:50 AM
Authorized User
 
Join Date: Mar 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old April 10th, 2007, 08:42 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old May 12th, 2007, 08:36 AM
Authorized User
 
Join Date: Mar 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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








Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert into Multiple Tables bmalex1 Beginning PHP 0 February 6th, 2006 12:45 PM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM
Importing Multiple files in Multiple tables Versi Suomi Access 6 June 1st, 2005 08:47 AM
Multiple Recordsets from Multiple Tables TSEROOGY Classic ASP Databases 2 December 28th, 2004 12:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.