SELECT DISTINCT with Joins?

I'm having a problem, getting too much data back from my query

I have three tables - one for documents (document), one for
documentviews (documentview) and third for connecting a specific news to
a specific documentview (doc2docview). The doc2docview table has a
structure of ID, owner (the document ID) and reference (the documentview
ID).

One document can be linked to several documentviews, which creates a
problem - with this query, if a document is assigned to three
documentviews, I get the same title three times.

SELECT DISTINCT document.title as title,
doc2docview.owner,
document.ID as did,
document.summary as summary,
document.source as source,
document.ctime as time,
documentpart.filename as filename
FROM doc2docview
LEFT JOIN document ON doc2docview.owner = document.ID
LEFT JOIN documentpart ON document.ID = documentpart.document
WHERE reference IN (55,56)
AND
document.status = 'A'
ORDER BY document.ctime DESC
LIMIT 100

What should I do?

Aapo V

Aapo V a écrit :
> I'm having a problem, getting too much data back from my query
>
> I have three tables - one for documents (document), one for
> documentviews (documentview) and third for connecting a specific news to
> a specific documentview (doc2docview). The doc2docview table has a
> structure of ID, owner (the document ID) and reference (the documentview
> ID).
>
> One document can be linked to several documentviews, which creates a
> problem - with this query, if a document is assigned to three
> documentviews, I get the same title three times.
>
> SELECT DISTINCT document.title as title,
> doc2docview.owner,
> document.ID as did,
> document.summary as summary,
> document.source as source,
> document.ctime as time,
> documentpart.filename as filename
> FROM doc2docview
> LEFT JOIN document ON doc2docview.owner = document.ID
> LEFT JOIN documentpart ON document.ID = documentpart.document
> WHERE reference IN (55,56)
> AND
> document.status = 'A'
> ORDER BY document.ctime DESC
> LIMIT 100
>
> What should I do?
>
> Aapo V

Relational database are relational not because they use links/relations
between tables, but because they are grounded on the relational algebra.
A relation is a set and as a set, it cannot have two identical elements.
The operator SELECT doesn't fullfill this requirement and in most cases
does not produce true relations.
On the contrary, SELECT DISTINCT insures that the result will have
distinct elements/lines (a true mathematical relation without duplicates).

SELECT doc.title, dv.owner FROM document doc INNER JOIN doc2docview dv
ON doc.ID=dv.ID

Since you specified fields from the document table AND from
documentview, some document titles may be various times in the resulting
relation but side by side with different owners from documentview. So,
the lines are still different.

You may, of course, suppress the fields from documentview in the SELECT
clause, and your document titles will be unique (an INNER JOIN with
fields from one of the tables is called a semi-join) but in this way you
cannot know the owner. In that case, the signification of your query is
to list all documents that have owner.

SELECT doc.title FROM document doc INNER JOIN doc2docview dv
ON doc.ID=dv.ID

What should you do ? Nothing or maybe use some extra tool like a report
generator which can realize a fine presentation of your data (in Access
for instance you have a good one). In SQL, you have to choose betwen
natural join with repetitions of field values or semi-join with loss of
information.

Hope it will help you.

Jacques