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.

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/

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

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

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?

Try:
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:
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