DISTINCT or DISTINCTROW does not work

[dubing]

Hello everyone,

I've tried both DISTINCT or DISTINCTROW in the following query in a PHP
script. But the results still contain duplicate records as show below.
DISTINCT works fine on single selected fields. But in this case, the
SELECT statement has multiple fields. Is this the correct way to use
DISTINCT on multiple fields?

$qry = odbtp_query("SELECT DISTINCT WebGradStudents.IDNo AS studentID,
LastName, FirstName, WorkPhone, Email, Office, Curriculum, areaID,
qryOtherProjects.Extra FROM WebGradStudents LEFT OUTER JOIN
qryOtherProjects ON WebGradStudents.IDNo = qryOtherProjects.studentID
ORDER BY LastName");

Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon
Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon

I'd appreciate any help.

Bing

[Arno R]

<du****@gmail.com> schreef in bericht news:11*********************@y43g2000cwc.googlegro ups.com...

Hello everyone,

I've tried both DISTINCT or DISTINCTROW in the following query in a PHP
script. But the results still contain duplicate records as show below.

Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon
Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon

This is NOT similar data ... look carefully, (paste in Notepad if you don't see it here ...)

Arno R

[Bri]

du****@gmail.com wrote:

Hello everyone,

I've tried both DISTINCT or DISTINCTROW in the following query in a PHP
script. But the results still contain duplicate records as show below.
DISTINCT works fine on single selected fields. But in this case, the
SELECT statement has multiple fields. Is this the correct way to use
DISTINCT on multiple fields?

$qry = odbtp_query("SELECT DISTINCT WebGradStudents.IDNo AS studentID,
LastName, FirstName, WorkPhone, Email, Office, Curriculum, areaID,
qryOtherProjects.Extra FROM WebGradStudents LEFT OUTER JOIN
qryOtherProjects ON WebGradStudents.IDNo = qryOtherProjects.studentID
ORDER BY LastName");

Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon
Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon

I'd appreciate any help.

Bing

Is this happening to all records? Or is is just a few of them? If its
just a few of them, check the fields that appear to be displaying as
blank (it seems that you have more fields in the SELECT than you have in
the example records, so I'm guessing that they are blank in the table).
If Zero Length Strings are allowed on those fields, then a ZLS will
'look' the same as a Null in the display but are not the same as far as
Distinct is concerned. If that's not it, then it might help to show us
the data in the tables that correspond to the sample output so someone
can try to duplicate he problem.

--
Bri

[Bri]

Arno R wrote:

<du****@gmail.com> schreef in bericht news:11*********************@y43g2000cwc.googlegro ups.com...

Hello everyone,

I've tried both DISTINCT or DISTINCTROW in the following query in a PHP
script. But the results still contain duplicate records as show below.

Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon
Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon

This is NOT similar data ... look carefully, (paste in Notepad if you don't see it here ...)

Arno R

Hmm, while I was looking at this myself to help out the OP, I didn't see
this difference in his original (it all lined up visually). It showed up
in the text I quoted in my reply (but I didn't notice it until seeing
your post and going back to it). It must be tab delimited or something?

--
Bri

[dubing]

Bri wrote:

Arno R wrote:

<du****@gmail.com> schreef in bericht news:11*********************@y43g2000cwc.googlegro ups.com...

Hello everyone,

I've tried both DISTINCT or DISTINCTROW in the following query in a PHP
script. But the results still contain duplicate records as show below.

Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon
Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon

This is NOT similar data ... look carefully, (paste in Notepad if you don't see it here ...)

Arno R

Hmm, while I was looking at this myself to help out the OP, I didn't see
this difference in his original (it all lined up visually). It showed up
in the text I quoted in my reply (but I didn't notice it until seeing
your post and going back to it). It must be tab delimited or something?

--
Bri

Thanks all who replied. I've found out why now. There is nothing
wrong with either DISTINCT or DISTINCTROW. Indeed, there are more
fields in the SELECT than in what actually show up. The returned rows
are truly distinct like this:

Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon value2 blah
blah
Zhen, Don Construction 211-4321 zh***@some.edu 403 Bayon value1 blah
blah

The first several fields are identical and just what I needed to
display. That's what puzzled me. All is fine now.

Bing