Is Outputting two tables with Join my best option?

[Guest]

I have two tables:

The first table’s primary key is sireID

It holds the picture of the multiple sires, their name, the owner’s name and a brief description

The second table holds the trial scores of the individual dogs, joined by the sireID from table one.

I tried using a join to output the dog’s info and it’s scores :

<cfquery name="getDetailsQuery1" datasource="#application.database#">

select *

from sires

order by sireID

</cfquery>

<cfquery name="getDetailsQuery2" datasource="#application.database#">

select *

from scores

where sireID = #getDetailsQuery1.sireID#

</cfquery>

<CFQUERY NAME="getDetails" datasource="#application.database#">

SELECT * FROM getDetailsQuery1

UNION

SELECT * FROM getDetailsQuery2

ORDER BY sireID

</CFQUERY>

I get an error message:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'getDetailsQuery1'. Make sure it exists and that its name is spelled correctly.

Other than switching over to mySQL is there a better way to query this?

I am trying to create the following

(First Table)

Dog’s Picture

Owner

Description

(Second Table)

Table of the trial scores for this dog

Next record

[Carl Von Stetten]

rickaclark54,

The short answer is yes, a Join would be the preferred way to handle this.  Also, mySQL would definitely be a step up from using Microsoft Access.  Most developers would recommend not using Microsoft Access in a production system.

That being said, there are a number of problems with the third query which result in the error you are seeing, as well as other errors that will likely be thrown once that is resolved:

1. You are attempting a query-of-query statement.  You can't do that against an actual database.  You need to change the CFQUERY tag from this:
   <CFQUERY NAME="getDetails" datasource="#application.database#"> to this:
   <CFQUERY NAME="getDetails" dbtype="query">.
2. You are blindly unioning together two tables that likely have completely different structures by using SELECT *.  Unions can only be used when the SELECT statements select a matching set of columns in two tables (by matching I mean same number of columns and same data types).  And UNIONS essentially append the results from the second query to the results from the first query, which I don't think you want.

I would definitely try a single query using an appropriate JOIN clause.  I would also suggest selecting only the columns you need from either table rather than using SELECT *, especially if both tables have any columns with the same name.

HTH,

-Carl V.

[Guest]

Hi Carl, sorry it took so long to get back to your comment. Actually, the first table has all of the information about the do's attributes. The second table has all of its field trial winnings in the second table, with the dog's id joining the two tables together. I changes the line like yo suggested to  dbtype="query" and received my next error:

Query Of Queries runtime error.
All queries in an SQL SELECT statement that contains a UNION operator must have an equal number of expressions in their result list, which is the reason I used the * in the queries, because all data will output. Below is a link to the page taht will show how the dogs will be displayed:

Sterndogs

The query for the sample was for just the one sample. However, I need it to list all sire dogs. I hope this helps explaing better what I am trying to accomplish.

Thanks for your input.

[Carl Von Stetten · Accepted solution]

Rick,

Yes, that would be issue #2 that I identified.  Now that you've explained what is in each table, it is clear that a UNION is not the appropriate way to go about this.  You need to do a JOIN.  As I suggested, you want to try to do this in your initial query to the database and then you won't need to do any query-of-query at all.  Here is a pseudo-query (since I don't know what your columns are):

SELECT  sires.sireID
               ,sires.picture
               ,sires.owner
               ,sires.description
               ,scores.columnA
               ,scores.columnB
               ,scores.columnC
FROM sires
INNER JOIN scores on sires.sireID = scores.sireID

Keep in mind, you'll potentially get multiple records back for each match of a sireID from the "sires" table to corresponding rows in the "scores" table (I'm guessing that there is a one-to-many relationship between "sires" and "scores").

-Carl V.

[Guest]

Carl,

Thanks for the tip on Inner Joins. I grouped the dogs by the dog's name and used the join for the tiral information and it worked perfectly.

[Carl Von Stetten]

Rick,

Glad that worked for you!

-Carl V.