Need help with SQL Select statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Posted - 11/12/2003 : 02:22:40
-------------------------------------------------------------------------------

Im trying to use this statement

SELECT Distinct D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePublished,DTR.DocumentID,DT.DocumentType,DT.ThumbNailImage from Documents D, DocumentSubjectAreas DSA, DocumentSubjectAreasREL DSAR, DocumentTypes DT, DocumentTypesRel DTR WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0 AND D.DocumentID=DSAR.DocumentID And DSAR.SubjectAreaID=DSA.SubjectAreaID And D.DocumentID=DTR.DocumentID AND DT.DocumentTypeID=DTR.DocumentTypeID Order By D.DatePublished des

The problem is that it doesnt return only distinct values for DocumentID. The table Documents, contains the DocumentID primary key column. The table DocumentTypesREL holds the relation between document types and documents. Therefore, a document may have multiple document types and therefore the DocumentID key will be found in DocumentTypesRel multiple times, however, I need to only select distinct DocumentID's from DocumentTypesRel. I cant seem to figure out why my statement isnt doing this. Can anyone help

Thanks, lanc
 
The scope of "Distinct" isn't just the field D.DocumentID,
but rather the whole record. That is, your query is
asking for every distinct *combination* of all of the
fields you're selecting. If there are two records in DT
with different DocumentType values but the same DocumentID
value, your query will retrieve both of them.

If you just need a list of the unique DocumentID's, you
could just Select Distinct D.DocumentID, but you must want
more than that. What do you need to retrieve?

-----Original Message-----
Posted - 11/12/2003 : 02:22:40
---------------------------------------------------------- ----------------------

Im trying to use this statement:

SELECT Distinct
Click to expand...
D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePu
blished,DTR.DocumentID,DT.DocumentType,DT.ThumbNailImage
from Documents D, DocumentSubjectAreas DSA,
DocumentSubjectAreasREL DSAR, DocumentTypes DT,
DocumentTypesRel DTR WHERE Instr(Lcase
(DSA.SubjectArea), 'new') > 0 AND
D.DocumentID=DSAR.DocumentID And
DSAR.SubjectAreaID=DSA.SubjectAreaID And
D.DocumentID=DTR.DocumentID AND
DT.DocumentTypeID=DTR.DocumentTypeID Order By
D.DatePublished desc
The problem is that it doesnt return only distinct values
Click to expand...
for DocumentID. The table Documents, contains the
DocumentID primary key column. The table DocumentTypesREL
holds the relation between document types and documents.
Therefore, a document may have multiple document types and
therefore the DocumentID key will be found in
DocumentTypesRel multiple times, however, I need to only
select distinct DocumentID's from DocumentTypesRel. I cant
seem to figure out why my statement isnt doing this. Can
anyone help?
 
The "Distinct" refers to the whole record, not just the ID
column.
You need to look at the records that you
consider "duplicate", find which columns are different,
and then determine, based on the differences, which record
you really want, and then change the query to eliminate
the unwanted records.
-----Original Message-----
Posted - 11/12/2003 : 02:22:40
---------------------------------------------------------- ----------------------

Im trying to use this statement:

SELECT Distinct
Click to expand...
D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePu
blished,DTR.DocumentID,DT.DocumentType,DT.ThumbNailImage
from Documents D, DocumentSubjectAreas DSA,
DocumentSubjectAreasREL DSAR, DocumentTypes DT,
DocumentTypesRel DTR WHERE Instr(Lcase
(DSA.SubjectArea), 'new') > 0 AND
D.DocumentID=DSAR.DocumentID And
DSAR.SubjectAreaID=DSA.SubjectAreaID And
D.DocumentID=DTR.DocumentID AND
DT.DocumentTypeID=DTR.DocumentTypeID Order By
D.DatePublished desc
The problem is that it doesnt return only distinct values
Click to expand...
for DocumentID. The table Documents, contains the
DocumentID primary key column. The table DocumentTypesREL
holds the relation between document types and documents.
Therefore, a document may have multiple document types and
therefore the DocumentID key will be found in
DocumentTypesRel multiple times, however, I need to only
select distinct DocumentID's from DocumentTypesRel. I cant
seem to figure out why my statement isnt doing this. Can
anyone help?
 
Dear Lance:

DISTINCT does not select only one record for the first column named,
but for all the columns. If you think about it, your expectation is
not really reasonable.

If there were only two columns, and only two records, what would you
expect it to do:

DocumentID FileName
1 Hello
1 Goodbye

According to your expectations, I think it could return either Hello
or Goodbye. Thus, the results would be ambiguous.

Hopefully, query results are never ambiguous. If you write a query
that really could give ambiguous results then it had better give you
an error!

If you want to show only one row in the results for each value of
DocumentID, then you must carefully place rules into the query that
specify how this is to be done. With the 4 tables you have joined in
your query, each join has the potential to multiply the number of rows
created. Each such case will require a section of coding to choose
either a singe row out of the many, or a set of aggregate values.

For future reference, I've posted your query below in a form I prefer
to read:

SELECT Distinct D.DocumentID, D.FileName, D.Title, D.ShortDescription,
D.DatePublished, DTR.DocumentID, DT.DocumentType,
DT.ThumbNailImage
FROM Documents D, DocumentSubjectAreas DSA,
DocumentSubjectAreasREL DSAR, DocumentTypes DT,
DocumentTypesRel DTR
WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0
AND D.DocumentID=DSAR.DocumentID
And DSAR.SubjectAreaID=DSA.SubjectAreaID
And D.DocumentID=DTR.DocumentID
AND DT.DocumentTypeID=DTR.DocumentTypeID
ORDER BY D.DatePublished desc

Posted - 11/12/2003 : 02:22:40
--------------------------------------------------------------------------------

Im trying to use this statement:

SELECT Distinct D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePublished,DTR.DocumentID,DT.DocumentType,DT.ThumbNailImage from Documents D, DocumentSubjectAreas DSA, DocumentSubjectAreasREL DSAR, DocumentTypes DT, DocumentTypesRel DTR WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0 AND D.DocumentID=DSAR.DocumentID And DSAR.SubjectAreaID=DSA.SubjectAreaID And D.DocumentID=DTR.DocumentID AND DT.DocumentTypeID=DTR.DocumentTypeID Order By D.DatePublished desc


The problem is that it doesnt return only distinct values for DocumentID. The table Documents, contains the DocumentID primary key column. The table DocumentTypesREL holds the relation between document types and documents. Therefore, a document may have multiple document types and therefore the DocumentID key will be found in DocumentTypesRel multiple times, however, I need to only select distinct DocumentID's from DocumentTypesRel. I cant seem to figure out why my statement isnt doing this. Can anyone help?

Thanks, lance
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
You need to use aggregation.

For example:

SELECT DocumentID, First(MyField1), First(MyField2)
INNER JOIN ...
WHERE ...
GROUP BY DocumentID

Grey
 
Back
Top