how do I write a join query that display's the joined table correctly

There's a few problems.  But the reason you're seeing duplicated rows is because you're missing the relationship between the 2 tables.  So the result is a cartesian product. You need to JOIN on the related column and put the filter in the WHERE clause

FROM tblCandidatePortfolioProjectFiles f INNER JOIN tblCandidatePortfolioProjectLinks l
         ON f.NameOfTheRelatedColumn =  l.NameOfTheRelatedColumn
WHERE f.req_part_id = <cfqueryparam value="#URL.req_part_id#" cfsqltype="cf_sql_integer">

Also, I'm not sure you really need a "grouped" output. Are you trying to generate some sort of grouped output like this?
ie
           Header
               -  item 1
               -  item 2
               -  item 3
           Header
               -  item 1
               -  item 2

Here's my guess as to what the join may be... and possible use of the group by..

But I could be wrong, as agx said... you need to determine what fields connect the tables and use them in the join.

<cfquery name="qexisting_filesB" datasource="#APPLICATION.datasource#">
SELECT  DISTINCT f.file_id
,f.req_part_id
,f.filename
,f.extension
,f.title
,f.description
,f.upload_date as adddt
,l.link_id
,l.req_part_id
,l.url
,l.text_title
,l.description
,l.adddt 
FROM tblCandidatePortfolioProjectFiles f 
 inner join tblCandidatePortfolioProjectLinks l on f.req_part_id = l.req_part_id
where f.req_part_id = <cfqueryparam value="#URL.req_part_id#" cfsqltype="cf_sql_integer">
</cfquery>

<cfoutput query="qexisting_filesB" group="file_id">
  #file_id# #filename# <br>
   <cfoutput>
    Link #link_id#<br />
  </cfoutput>
  <hr>
</cfoutput>

Select allOpen in new window

If you really need cfoutput "group", just remember .. you always have to order by the grouped field(s). Otherwise, there's a good chance it's won't work correctly.

ie
    ORDER BY f.file_id

Hi,

Thanks for the replies.

I tried gdemarie's example but still got the duplicate records.

So, I thought I could approach it from a different way using query of queries. It works until I add the Link_id to the output (then the dupes come back)

Would  something like this be a better approach?

Thanks!

<cfquery name="qexisting_filesB" datasource="#APPLICATION.datasource#">
SELECT  DISTINCT f.file_id
,f.req_part_id
,f.filename
,f.extension
,f.title
,f.description
,f.upload_date as adddt
,l.link_id
,l.req_part_id
,l.url
,l.text_title
,l.description
,l.adddt 
FROM tblCandidatePortfolioProjectFiles f 
 inner join tblCandidatePortfolioProjectLinks l on f.req_part_id = l.req_part_id
where f.req_part_id = <cfqueryparam value="#URL.req_part_id#" cfsqltype="cf_sql_integer">
</cfquery>


<cfquery name="qexisting_filesBpt2" dbtype="query">
SELECT  DISTINCT file_id
,req_part_id
,filename
,extension
,title
,description
,adddt
,link_id
FROM qexisting_filesB 
</cfquery>


<!---Then for the output--->

<cfoutput query="qexisting_filesBpt2">
  #file_id# #filename#  Link #link_id# <br/>
</cfoutput>

Select allOpen in new window

Is "req_part_id" the only related column?

I'm not so sure they're duplicates. Can you post a small dump of the data?

It doesn't really make sense to add a QofQ to this... it should give you the exact same results, if you had selected all the same columns.   But since you didn't select the same columns, you can just remove the columns that you left out from your original query....

This is your original query after removing the columns that you left out of your QofQ...   now you don't need the QofQ

<cfquery name="qexisting_filesB" datasource="#APPLICATION.datasource#">
 SELECT DISTINCT f.file_id
  ,f.req_part_id
  ,f.filename
  ,f.extension
  ,f.title
  ,f.description
  ,f.upload_date as adddt
  ,l.link_id
FROM tblCandidatePortfolioProjectFiles f
 inner join tblCandidatePortfolioProjectLinks l on f.req_part_id = l.req_part_id
where f.req_part_id = <cfqueryparam value="#URL.req_part_id#" cfsqltype="cf_sql_integer">
</cfquery>

Yes the req_part id is the only related column.

Plus, I dont want the link_id in with the file_id stuff. They are two separate things. So I want a list of the file_id and file_names and a list of the link_id and linkname (text_title) ordered by the adddt column.

So I think I need to union the tables instead of joining them.

Since you are joining two tables, you should see the same file_id and file name multiple times

File ID   FileName   Link ID
 1          file1            100
 1          file1            105
 1          file1            201


The above is not duplicate records... yes, please show us what your data is looking like

I think this is a display issue, not a query issue.   Can you give an example of what you want..

> I think this is a display issue, not a query issue

Agreed.   Please post a sample of the data AND desired output.

So the desired data would be something like:
file_id<br>
Filename <br>
title <br>
description

link_id
text_title
description

all ordered by the adddt column

But here is the way its actually being displayed
data.jpg

Oh, so for a particular part number, you want to get all the files and you want to get all the links associated with the part... there is NO relationship between files and links other than that they share the same parent (part number).

You should just use two queries... one for files and one for links.

I need to display them in the order that they were created. (adddt column)

Is that possible with two queries?

      > Is that possible with two queries?

Nope.  You were right the 1st time. You do need a UNION.  

    > so i got it to display correctly by using a union like this:

Just don't forget to ORDER BY addt .  Otherwise, it's just luck if the data ends up sorted correctly.

Since you solved it yourself, don't forget to mark your response as the solution.

agreed, you need a union

OK! Thank you so much for the help! I love this site!