Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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 November 3rd, 2005, 03:39 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select Distinct?

Hello Everyone and thanks for your help in advance. I am working on a application that needs to join two tables. The following query works for this purpose:

SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
    tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
    tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
    tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
               tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate

However, the problem I have now run into is that there appears to be many duplicate records in the database. The data is imported from a text file via a DTS package, so I am not aware of any way around it. So in order for my applicaiton to work, I need to be able to filter out the duplicates. I know that Select Distinct is available, but can't figure out how to get this to work with this query. Any help on this issue would be greatly appreciated. Thanks.

 
Old November 3rd, 2005, 04:53 PM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Are you sure you need a cross join?
How about a regulat join (take out the keyword cross)

SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
    tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
    tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
    tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments JOIN
               tblPTDEMO2
ON(tblAppointments.PatientID = tblPTDEMO2.MRNumber)
WHERE tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
 
Old November 4th, 2005, 11:07 AM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the response. Removing the CROSS causes a sysntax error.

 
Old November 4th, 2005, 11:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

The SQL standard (92) states that if no join type is specified, then an INNER JOIN is assumed by default. A join type is one of the keywords INNER, FULL, LEFT, and RIGHT (plus at least one other that nobody implements). Thus, the keyword JOIN is required, but the others are optional, and if none appear, an INNER join is assumed.

What SQL implementation are you using, because it would appear that it does not conform (or you have a typo) ...

You really, really don't want a cross join involving any table of any size ...



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old November 5th, 2005, 09:39 AM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am using SQL Server 2000, so I doubt there is anything weird. Here is the query that works pasted directly from query analyzer:

SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
    tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
    tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
    tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
               tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate

Like I said previously, when I remove the CROSS keyword, I get a syntax error. Any ideas?

 
Old November 5th, 2005, 09:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Try:
Code:
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber,
    tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
    tblAppointments.PatientDOB, tblAppointments.PatientSex,
    tblAppointments.NewPatient, tblAppointments.HomePhone,
    tblAppointments.WorkPhone, tblAppointments.Insurance1,
    tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
    tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3,
    tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate,
    tblAppointments.ApptTime
FROM tblAppointments INNER JOIN tblPTDEMO2 
        ON tblAppointments.PatientID = tblPTDEMO2.MRNumber
WHERE tblAppointments.Insurance1 = 'MED'
    AND tblAppointments.ApptTypeID <> 'MTG'
    AND tblAppointments.ApptTypeID <> 'PNV'
    AND DateDiff("dd", ApptDate, GetDate()) = 0
ORDER BY tblAppointments.ApptDate
Note the ON clause - presumably these are the columns that relate the two tables to each other. That is, a tblPTDEMO2.MRNumber is in fact a tblAppointments.PatientID, right? If not, then how do these two tables relate to each other?

Alternatively, the older deprecated syntax of a JOIN relates the two tables in the WHERE clause as you had done, but the FROM clause simply lists the tables:
Code:
...
FROM tblAppointments, tblPTDEMO2
WHERE tblAppointments.PatientID = tblPTDEMO2.MRNumber
...
and has no ON clause.

One thing that's puzzling; your original attempt included the two columns tblAppointments.PatientID and tblPTDEMO2.MRNumber in the SELECT clause, and included in the WHERE clause the predicate 'tblAppointments.PatientID = tblPTDEMO2.MRNumber'. Since these were to be equal, why would you return the same value twice in the SELECT list?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Distinct kirkmc Access 13 July 13th, 2006 01:26 PM
Select Distinct kirkmc Excel VBA 3 May 5th, 2006 07:55 PM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM
select distinct bmains ADO.NET 0 April 8th, 2004 02:50 PM
Select distinct elements andrin XSLT 2 August 4th, 2003 04:07 AM





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