SELECT DISTINCT query is not deduplicating

[shannonsider]

Hi,

I am running a 'SELECT DISTINCT' query that is not deduplicating. I am using
Microsoft Access 2000.

I have two tables; one is called 'places' and the other is called
'description'.

Places has three fields called description, place_no, and place. This is how
it looks.

place_no place description
2696 Dún Mór 4313
22697 Dunmore 4313
22699 Dunmore 4313
22700 Downamore 4313
22701 Dunmore 4313
22702 Dunmore 4313

The second table is called description; it has two fields called
description_no, and situation.

It looks like this.

description_no situation
4313

The query

SELECT DISTINCT places.place, description_no, situation
FROM places LEFT JOIN description ON
places.description=description.description_no
WHERE place='dunmore'
ORDER BY places.place;

is returning

place description_no situation
Dunmore 4313
Dunmore 4313
Dunmore 4313
Dunmore 4313

It is not deduplicating despite the 'SELECT DISTINCT'.

I can do three things to make it work.

1. Remove the 'ORDER BY places.place' clause
2. 'description_no' in the description table is a primary key. If I change
this it works
3. If I put any value into the situation field rather than leave it null.

None of these are great options.

I am wondering what is going on?

Any help would be much appreciated?

Regards
John

 

[scubadiver]

Q1) On what fields do you want the rows to be based?

Q2) I presume you have a 1-to-many relationship between 'places' and
'description'?

 

[Michel Walsh]

SELECT DISTINCT works on the SELECTED fields, note the plural form, not on
ONE field you have "in mind".


If you use

SELECT DISTINCT primaryKeyField, someOtherField FROM ...

definitively, since each primaryKeyField value different, the DISTINCT won't
do much. Each record will be returned, even if you have in mind, distinct
someOtherField only.


If you use

SELECT DISTINCT f1, f2 FROM somewhere

then each possible COUPLE {f1, f2} would be kept only once. It does not
return only DISTINCT {f1} and DISTINCT {f2}. It returns DISTINCT {f1,
f2}, as couple. As example, {4, 5} and {4, 6} are distinct, even if 4 is
repeated twice... that does not matter, it is the couple, like {FristName,
LastName} that defines distinct people, not just their first name, not just
their last name.



In the case of NULL, a NULL is NOT equal to another NULL. A null is not
different than another NULL. So, question, are two NULL ... distinct ?

In theory, by the SQL standard, two nulls are NOT distinct, but, to make a
story short, there are cases where Jet is buggy about it. Use a GROUP BY to
avoid the problem:

SELECT f1, f2 FROM somewhere GROUP BY f1, f2

should NOT duplicate null, while it is possible to have duplicated nulls
with

SELECT DISTINCT f1, f2 FROM somewhere

but not always. Generally, it got it right, but if one of the field has an
index not allowing duplicated value, then the SELECT DISTINCT will wrongly
assume that no duplicate == distinct. It is not. A null does not duplicate
another null, but a null is not distinct to another null. Two different
concepts: duplication (based on equal) and distinct (based on grouping),
that Jet specifications mixed up in that case. Note that MS SQL Server 2005
and previous, also messed up the definition, since it does not, wrongly,
allow two null under an no dup index.



Vanderghast, Access MVP