How can I display content from two tables on a single row? 2

[josel]

Howdy frients!

I finally got my problems with <CFFILE ...> worked out. Thank you all for your help !!!!

Now, my new *WALL* is displaying a column from table2 while running query on table1. I have two tables, one identifies the technicians and the other the help desk entries.

On the help desk table (table1), instead os holding the technician's name, I am holding an ID (numeric). I use our standard <cfquery ...> and <cfoutput ...> to display a <table ...> with rows and columns form a grid, if you will.

The first column should display the technician. Since my query is on table1 and I only have the ID, I can't (don't know how) show the technician's name thus making it possible to know exactly who got what ...

How can I extract the technician's name from table2 and show it on column 1 of my query result?

Should/Can I run a <cfquery ...> within the <cfoutput ...>?

Thank you all in advance!

Regards;


Jose Lerebours
KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[webmigit]

Select Techs.UserID as theAuthor, Entries.* FROM Techs,Entries
WHERE Techs.TechID=Entries.AuthorID

And then you can use the the variable #theAuthor# in the cfoutput associated with the query.

 

[josel]

webmigit,

I'm not sure I understand what you mean. Can you please restructure your suggested syntax? Where do I do this, within or outside <cfoutput ...>?

Keep in mind that I am showing all projects for all mechanics and each project may have a different mechanic.

Thanks;


Jose Lerebours
KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[webmigit]

This will be your whole query... the sql I posted above.. you can also put:

AND entryID=#url.entryID# or whatever to get one specific row..

I had to guess at your column names.. Anyway you could nest a cfquery for each row.. but that uses too much load for a simple operation.. with the code I posted above you get all the data you need.

Hope this Helps,
Tony

 

[imstillatwork]

would a structure of names/IDs help? That way you can have all the Name / ID combinations ready to use, but only have to query for them once.

 

[josel]

webmigit,

Please bare with me! I'm sorry but your suggestion is still beyond my understanding. At the, I'm sure I'll look back and realize how simple it was.

This is what my query looks like:

<cfquery name=&quot;GetProjects&quot; datasource=&quot;#DSN#&quot; dbtype=&quot;odbc&quot;>
select *
from projects
</cfquery>

NOTE: All fields have *proj* as prefix. So, my technician in this table would be ProjTechID.

My second table, the technicians, is named &quot;techs&quot;. The ID field is named TechID.

Given your suggested query and above query, how can I then do as you suggest?

All columns, but first column, displayed on screen come from *projects* table. The first column is where I want to display TechCode from *techs* table.

Once again, thank you for your help and my opologies for my lack of understanding.

Regards;


Jose Lerebours

KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[webmigit]

<cfquery name=&quot;GetProjects&quot; datasource=&quot;#DSN#&quot; dbtype=&quot;odbc&quot;>
SELECT Techs.TechName as theAuthor, Projects.*
FROM Techs,Projects
WHERE Techs.TechID=ProjTechID
</cfquery>

I don't know what the username filed in the techs table is.. but you need to change techs.techname to techs.yourusernamefieldwhateveritmaybe..

This query should work though.. for instance with the above query to get all authors who posted a project.. you'd do this:

<CFOUTPUT query=&quot;GetProjects&quot;>
#theAuthor#<BR>
</CFOUTPUT>

You have access in your cfoutput to all fields in projects and to theauthor in techs with this query.

Tony

 

[pcorreia]

I find that the ANSI SQL syntax is not only a best practice, but a bit easier to understand what's going on. To rephrase webmigit's query in ANSI syntax:

<cfquery name=&quot;GetProjects&quot; datasource=&quot;#DSN#&quot; dbtype=&quot;odbc&quot;>
  SELECT 
    T.TechName as theAuthor, P.*
  FROM 
    Techs AS T
    INNER JOIN Projects AS P on T.TechID = P.ProjTechID
</cfquery>

As webmigit was explaining, what this does is creates a result set which includes both the TechName column from the Techs table, as well as all the columns from the Projects table, and includes only the rows where the ProjTechID matches the TechID from the Techs table.

Explaining the principle of SQL JOINs may be beyond the scope of this forum; I would suggest as a reference the excellent (and inexpensive) book:

Sam's Teach Yourself SQL in 21 Days

http://www.amazon.com/exec/obidos/ASIN/0672316749/qid=/102-9052433-4088110

 

[imstillatwork]

actualy, i think it is logicaly easier to understand the other way. I want to select all this stuff, but only where this = that, and this = that, ....

 

[webmigit]

I use my method (well not mine, but that of many developers) because I can still use the same where code that I've always used...

< and > and = and AND NOT, AND, and OR.. its all the same, just prefixed with the table name.. when you're working 14.5 hours a day on one site, you don't have alot of time to learn anything you don't have to know for the job.. So once I found out how to do a relationship like this, I could use the sql I've known for years in another dimension of cold fusion.

I'm sure that ANSI has its uses, and just so I can say I know it when I'm done with this site, I might learn it, but its priority right now to code fast without having to distract myself with reinventing the wheel when the wheel already works pretty good.

 

[pcorreia]

Sorry, I didn't mean to touch off a holy war about ANSI syntax vs. proprietary Microsoft syntax. However, I do still think that someone who is learning SQL now should be learning ANSI syntax, since it's supported now by all the major vendors (including Microsoft), making it totally portable from system to system.

The old syntax of joins is hard to maintain because it doesn't cleanly separate join logic from filter logic. There were also some problems with the old syntax, including that in certain cases it was possible to build ambiguous queries. Most compellingly, Microsoft has stated that it will not support the old syntax indefinitely.

http://www.microsoft.com/sql/using/tips/development/July23.asp

 

[imstillatwork]

good information to know. Also good to know both ways of doing it, wether you use one way or the other.

 

[josel]

webmigit/pcorreia,

I have tried both syntax and I am getting an error

**************************************************
Error Diagnostic Information
QUERY

The QUERY attribute of the tag does not specify the name of an available query

The error occurred while processing an element with a general identifier of (CFOUTPUT), occupying document position (98:4) to (98:30).
****************************************************

I tried webmigit syntax first and then pcorreia and they both have same problem. Here is what the query looks like (oh, now they want to know who posted it not who's working on it - but it is all the same):

<cfquery name=&quot;GetProjects&quot; datasource=&quot;#application.DSN#&quot; dbtype=&quot;odbc&quot;>
SELECT
T.UserCode as PostedBy, P.*
FROM
users AS T
INNER JOIN projects AS P on T.UserID = P.ProjUserID
<cfif session.userLevel IS 0>
where ProjCompID = #session.compID#
<cfelse>
where ProjCompID = #session.compID# AND ProjUserID = #session.userID#
</cfif>
ORDER BY ProjDatePosted DESC
</cfquery>

The <cfouput ...> query looks like this:

<cfoutput query=&quot;ProjList&quot;>

<tr bgcolor=&quot;#iif(ProjList.currentrow MOD 2, DE('FFFFFF'), DE('lightgrey'))#&quot;>

<!-- This column shows Posted By Code -->
<td width=&quot;10%&quot; class=&quot;grid&quot;>#PostedBy#</td>
^^^^^^^^^
<td width=&quot;10%&quot; class=&quot;grid&quot;>#ProjList.ProjDatePosted#</td>
<td width=&quot;5%&quot; class=&quot;gridright&quot;>#ProjList.ProjID#</td>
<td width=&quot;50%&quot; class=&quot;grid&quot;>#ProjList.ProjSubject#</td>

<td width=&quot;5%&quot; class=&quot;gridcenter&quot;>

<cfif #ProjList.ProjAttachment# IS NOT &quot;&quot;>
<img src=&quot;#application.root##application.images#ckmark.gif&quot; width=&quot;20&quot; heigh=&quot;22&quot;>
<cfelse>
&nbsp;
</cfif>

</td>

</tr></cfoutput>


What am I missing? I followed both syntax (cut/paste) and I cannot get the queries/cfoutput to work.

I truly appreciate your help with this.

Regards;


Jose Lerebours KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[webmigit]

Your CFOUTPUT query attribute names an invalid query.. give it the name of the query we gave the cfquery. &quot;GetProjects&quot;

 

[josel]

Talk about keeping your eyes fixed on one spot!

I was so into understanding both your syntax and application for the queries that I totally overlooked the simple things ... (part of the problem with CUT/PASTE )

webmigit, thank you very much for your time and incretible patience.

Regards;


Jose Lerebours


KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours