Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.

Reply With Quote
  #2 (permalink)  
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/
Reply With Quote
  #3 (permalink)  
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.

Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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?

Reply With Quote
  #6 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:04 AM.


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