• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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

Hi,

I have a query that joins two tables

The problem is that when I output the results only the first row of the joined table is getting displayed (multiple times).  So, if I want to display the file_id and link_id from both tables and there are 3 file_id's in the first table the link_id will just display three times from the first row.

I have included the code for better clarification

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 JOIN tblCandidatePortfolioProjectLinks l on 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# #link_id#<br /></cfoutput>

Open in new window

0
smfmetro10
Asked:
smfmetro10
  • 6
  • 6
  • 5
1 Solution
 
_agx_Commented:
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

0
 
gdemariaCommented:
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>

Open in new window

0
 
_agx_Commented:

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

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
smfmetro10Author Commented:
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>

Open in new window

0
 
_agx_Commented:
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?
0
 
gdemariaCommented:
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>


0
 
smfmetro10Author Commented:
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.
0
 
gdemariaCommented:
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

0
 
gdemariaCommented:
I think this is a display issue, not a query issue.   Can you give an example of what you want..
0
 
_agx_Commented:
> I think this is a display issue, not a query issue

Agreed.   Please post a sample of the data AND desired output.
0
 
smfmetro10Author Commented:
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
0
 
smfmetro10Author Commented:
so i got it to display correctly by using a union like this:

<cfquery name="qtest" datasource="#APPLICATION.datasource#">
SELECT 'file' AS type, file_id AS id,filename AS title,description,req_part_id
FROM tblCandidatePortfolioProjectFiles
where req_part_id = <cfqueryparam value="#URL.req_part_id#" cfsqltype="cf_sql_integer">
union
SELECT 'link' AS type, link_id AS id,text_title AS title,description, req_part_id
from tblCandidatePortfolioProjectLinks
where req_part_id = <cfqueryparam value="#URL.req_part_id#" cfsqltype="cf_sql_integer">
</cfquery>
correct-data.jpg
0
 
gdemariaCommented:
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.

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

Is that possible with two queries?
0
 
_agx_Commented:
      > 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.
0
 
gdemariaCommented:
agreed, you need a union
0
 
smfmetro10Author Commented:
OK! Thank you so much for the help! I love this site!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now