If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SELECT DISTINCT query is not deduplicating
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 |
#2
|
|||
|
|||
SELECT DISTINCT query is not deduplicating
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'? "shannonsider" wrote: 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 |
#3
|
|||
|
|||
SELECT DISTINCT query is not deduplicating
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 "shannonsider" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|