Question
How to use sql to get two pieces of data in one cfquery
OK, that was probably a bad title, but ...
I am building a new "report" page for an existing application. In that application, I have three tables that hold information about a "request".
One table (tblstatus) holds "status" values with "stat_id" being the primary key and "status" being the value. So, stat_id 1=status "new", stat_id 2=status "denied", stat_id 3=status "approved", etc.
A second table (tblcssp) has information put in by a person with approval authority on this request. The table has a field (cssp_stat_id) that gets a 2 or 3 based on that person's determination.
The third table (tblcsfp) holds the same information by a third person who also has approval authority. It too has a field (csfp_stat_id) that holds a 2 or 3 based on that person's determination.
I need to output one line of information for each request that shows the status of each request with the approval or disapproval of each of those two people. But, I can't figure out how to do this without getting a conflict within my cfquery. If I use:
<cfquery name=qGetSTAT>
select stat_id, status, cssp_stat_id, csfp_stat_id
from tblstatus, tblcssp, tblcsfp
where tblstatus.stat_id=tblcssp.cssp_stat_id and tblstatus.stat_id=tblcsfp.csfp_stat_id
</cfquery>
the table only shows the translation of the determination from the tblcssp table in both of the fields. Basically, the translation of the data in the tblcsfp table never gets shown.
How can I take the data from two different tables and translate it via the one "status" table and display them both on one line of a table. I have not included all the html because that's not an issue.
Do I need to do two different queries? If so, how do I include the output in one cfoutput?
By the way, the tblcssp and tblcsfp tables always have the same number of rows. They are currently up to 995 rows each.
Thank you.
I am building a new "report" page for an existing application. In that application, I have three tables that hold information about a "request".
One table (tblstatus) holds "status" values with "stat_id" being the primary key and "status" being the value. So, stat_id 1=status "new", stat_id 2=status "denied", stat_id 3=status "approved", etc.
A second table (tblcssp) has information put in by a person with approval authority on this request. The table has a field (cssp_stat_id) that gets a 2 or 3 based on that person's determination.
The third table (tblcsfp) holds the same information by a third person who also has approval authority. It too has a field (csfp_stat_id) that holds a 2 or 3 based on that person's determination.
I need to output one line of information for each request that shows the status of each request with the approval or disapproval of each of those two people. But, I can't figure out how to do this without getting a conflict within my cfquery. If I use:
<cfquery name=qGetSTAT>
select stat_id, status, cssp_stat_id, csfp_stat_id
from tblstatus, tblcssp, tblcsfp
where tblstatus.stat_id=tblcssp.cssp_stat_id and tblstatus.stat_id=tblcsfp.csfp_stat_id
</cfquery>
the table only shows the translation of the determination from the tblcssp table in both of the fields. Basically, the translation of the data in the tblcsfp table never gets shown.
How can I take the data from two different tables and translate it via the one "status" table and display them both on one line of a table. I have not included all the html because that's not an issue.
Do I need to do two different queries? If so, how do I include the output in one cfoutput?
By the way, the tblcssp and tblcsfp tables always have the same number of rows. They are currently up to 995 rows each.
Thank you.
AdChoices