Need Help with SQL Select/Join Statement

[maxf]

Im having a SQL nightmare.

Simply put, I have a table, "Documents" which has a uniqueID for each record "DocumentID", and another table, "DocumentTYpes", with a uniqueID, "DocumentTypeID", and the tables are linked via another table, "DocumentTypesREL", which has two columns, "DocumentID" and "DocumentTypeID" (foreign keys for the respective tables). Documents can therefore have multipe document types:

DocumentTypesRel (Data)

DocumentID DocumentTypeID
1 2
2 4
1 3

If you wanted to return a recordset so that you only show a record once (Distinct DocumentID), how would you do it? When I join these tables, because of the duplicate values for DocumentID 1, in this example, this record is displayed twice in the recordset? So, my question is how do you display records only once when you are joining tables using a linked table such as this where foreign keys can appear multiple times?

Thanks for any help,
lance

 

[nicsin]

Hi Lance,

are you saying that the "distinct" doesn't work? can you post your query here as well?

regards,
nicsin

 

[lespaul]

You're right if you use the DISTINCT key word on:

SELECT DISTINCT DocumentID, DocumentType FROM DocumentTypesREL

the record set you showed would be the proper result. Why? Because when you look at your results each COMBINATION of DocumentID & Type are Unique. There's a Document#1 with two different Types. Each one is unique.

How do you want to determine which of the records to return? The largest document type? the least document type? If you are going to join into DocumentTypesREL then you need to decide which of those two DIFFERENT records you want to choose.

HTH

Leslie