URL Method for 3 tables?

[bonneylake]

Hey Everyone,

i been working on trying to understand the URL method of retrieving information for the last week, but i am stuck. I been able to get one table of information, but now i need to get 3 tables of information at the same time an i am running into trouble. Each table has a number in common with the other, each has a field that holds the number (but fields have different name). For example ticketMaster table has the field pk_ticketID which holds the number 1, serial has the field pkb_fk_ticketNo which holds the number 1, and parts has the field fk_ticketNo which holds the number 1. Right now i can get the correct table information for ticketMaster but for the serial an parts i get all the records that are in those tables instead of just the one record needed, which in this case is the one record that holds the number 1.

Right now here is what i have

 <!---Shows what was previously entered into table ticketmaster--->
<cfquery name="ticket" datasource="CustomerSupport">
 SELECT pk_ticketID,title,priority,status,
cost_center,fk_customer_number,
customer_company,customer_Fname,customer_Lname,
customer_add1,customer_city,customer_state,
customer_zip,customer_email,customer_pri_phone,
customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>

<cfquery name="serial" datasource="CustomerSupport">
SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type ,
software_hardware,resolution,resolution_date,
verification_date,rma_data,
type_hardware_failure,dept_responsibility,resoluti on_verified_by FROM dbo.tbl_CS_serial
</cfquery>
 
<cfquery name="parts" datasource="CustomerSupport">
SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,
part_returned,defective,submission
FROM dbo.tbl_CS_parts
</cfquery>
 
<form name="page1" id="page1" action="saveticket1edit.cfm?<cfoutput query="ticket">pk_ticketID=#pk_ticketID#</cfoutput><cfoutput query="serial">&pkb_fk_ticketNo=#pkb_fk_ticketNo#</cfoutput><cfoutput query="parts">&fk_ticketNo=#fk_ticketNo#</cfoutput>"
 method="POST" onSubmit="return validate_form();">
</form>

if anyone could explain to me how to get the url method to work with 3 fields in 3 different tables i would really appreciate it.

Thank you in advance,
Rach

[acoder]

You could combine all three into one query and perform a join operation. The best way would depend on the database and the dialect of SQL used, but a very simple way would be something like:

select ...
from ticket, serial, parts
where serial.fk_id = ticket.id and parts.fk_id = ticket.id
and ticket.id = #url...#

You match the foreign keys with the primary key of the main table to avoid duplicates.

[bonneylake]

You could combine all three into one query and perform a join operation. The best way would depend on the database and the dialect of SQL used, but a very simple way would be something like:

select ...
from ticket, serial, parts
where serial.fk_id = ticket.id and parts.fk_id = ticket.id
and ticket.id = #url...#

You match the foreign keys with the primary key of the main table to avoid duplicates.

Hey Acoder,

Well here is what i have, but its still not getting the right information, it still getting all the rows in the tables for serial and parts

<!---Shows what was previously entered into table ticketmaster--->
<cfquery name="ticket" datasource="CustomerSupport">
		SELECT pk_ticketID,title,priority,status,cost_center,fk_customer_number,
        customer_company,customer_Fname,customer_Lname,customer_add1
,customer_city,customer_state,
        customer_zip,customer_email,customer_pri_phone,
customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
        WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>

<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware,
resolution,resolution_date,
         verification_date,rma_data,type_hardware_failure,dept_responsibility,
resolution_verified_by FROM dbo.tbl_CS_serial

</cfquery>

<cfquery name="parts" datasource="CustomerSupport">
		SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
         FROM dbo.tbl_CS_parts
</cfquery>


<cfquery name="all" datasource="CustomerSupport">
		SELECT *
       FROM dbo.tbl_CS_ticketMaster,dbo.tbl_CS_serial,dbo.tbl_CS_parts
        WHERE pkb_fk_ticketNo = pk_ticketID and pk_partID = pk_ticketID
        and pk_ticketID =  #URL.pk_ticketID#
</cfquery>

<cfoutput query="all">
<form name="page1" id="page1" action="saveticket1edit.cfm?pk_ticketID=#pk_ticketID#"
method="POST" onSubmit="return validate_form();">
</cfoutput>
</form>

Thank you,
Rach

[acoder]

What's the relationship between serial/parts and ticket. Is it many-to-one, i.e. many serials and parts to one ticket?

[bonneylake]

What's the relationship between serial/parts and ticket. Is it many-to-one, i.e. many serials and parts to one ticket?

Ok everytime someone inserts a ticket. ticket has one record (always), parts has one record (always) and serial can have multiple records. An there all suppose to share the same ticket like pkb_fk_ticketNo (for serial) has the same number as fk_serialNo (for parts) and pk_ticketID(for ticket). Hope that makes since, kinda hard to explain.

Thank you,
Rach

[bonneylake]

Hey Acoder,

Yes its many serials and parts to one ticket. They all share the same number in each table like if pk_ticketID (from ticket table) has the number 1, then pkb_fk_ticketNo (from serial table) has the number 1, and fk_ticketNo(fro m parts table) has the number 1. I figured out i had the wrong field for parts. But even changing it didn't seem to help. But here is the statement again

<cfquery name="all" datasource="CustomerSupport">
		SELECT *
       FROM dbo.tbl_CS_ticketMaster,dbo.tbl_CS_serial,dbo.tbl_CS_parts

        WHERE pkb_fk_ticketNo = pk_ticketID and fk_ticketNo = pk_ticketID
        and pk_ticketID =  #URL.pk_ticketID#
</cfquery>

Thank you again for all your help,
Rach

[acoder]

To make things easier, you could use 3 queries as you had earlier.

As an example:

<cfquery name="serial" datasource="CustomerSupport">
SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type ,
software_hardware,resolution,resolution_date,
verification_date,rma_data,
type_hardware_failure,dept_responsibility,resoluti on_verified_by FROM dbo.tbl_CS_serial
WHERE pkb_fk_ticketno = #url.pk_ticketID#
</cfquery>

Then you can get the serial and parts data separately.

[bonneylake]

To make things easier, you could use 3 queries as you had earlier.

As an example:

<cfquery name="serial" datasource="CustomerSupport">
SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type ,
software_hardware,resolution,resolution_date,
verification_date,rma_data,
type_hardware_failure,dept_responsibility,resoluti on_verified_by FROM dbo.tbl_CS_serial
WHERE pkb_fk_ticketno = #url.pk_ticketID#
</cfquery>

Then you can get the serial and parts data separately.

Hey Acoder,

Thought i understood what you meant, but i am still having trouble.It still wont only get the serial and part associated with the ticket Here is what i have.

<cfquery name="ticket" datasource="CustomerSupport">
		SELECT pk_ticketID,title,priority,status,cost_center,fk_customer_number,
        customer_company,customer_Fname,customer_Lname,customer_add1,
customer_city,customer_state,
        customer_zip,customer_email,customer_pri_phone,customer_sec_phone,
customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
        WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>

<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware
,resolution,resolution_date,
         verification_date,rma_data,type_hardware_failure,dept_responsibility
,resolution_verified_by FROM dbo.tbl_CS_serial
          WHERE pkb_fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<cfquery name="parts" datasource="CustomerSupport">
		SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective
,submission
         FROM dbo.tbl_CS_parts
         WHERE fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<cfoutput>
<form name="page1" id="page1" action="saveticket1edit.cfm?pk_ticketID=#pk_ticketID#"
method="POST" onSubmit="return validate_form();">
</cfoutput>

Any suggestions?

Thank you for all the help :),
Rach

[acoder]

Where's the code for outputting the data?

[bonneylake]

Where's the code for outputting the data?

Hey Acoder,

Not sure what you mean, bit confused. i don't know if this is what you mean but here is all i have

page 1

<!---Shows what was previously entered into table ticketmaster--->
<cfquery name="ticket" datasource="CustomerSupport">
		SELECT pk_ticketID,title,priority,status,cost_center,fk_customer_number,
        customer_company,customer_Fname,customer_Lname,customer_add1,customer_city,customer_state,
        customer_zip,customer_email,customer_pri_phone,customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
        WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>

<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware,resolution,resolution_date,
         verification_date,rma_data,type_hardware_failure,dept_responsibility,resolution_verified_by FROM dbo.tbl_CS_serial
          WHERE pkb_fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<cfquery name="parts" datasource="CustomerSupport">
		SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
         FROM dbo.tbl_CS_parts
         WHERE fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<cfoutput>
<form name="page1" id="page1" action="saveticket1edit.cfm?pk_ticketID=#pk_ticketID#"
method="POST" onSubmit="return validate_form();">
</cfoutput>

an heres whats on page 2

<cfquery name="ticket" datasource="CustomerSupport">
		SELECT pk_ticketID,title,priority,status,cost_center,followup_date,fk_customer_number,
        customer_company,customer_Fname,customer_Lname,customer_add1,customer_city,customer_state,
        customer_zip,customer_email,customer_pri_phone,customer_sec_phone,customer_notes,onsite_flag,number_onsite,htpp FROM          dbo.tbl_CS_ticketMaster
        WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>

<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware,resolution,resolution_date,
         verification_date,rma_data,type_hardware_failure,dept_responsibility,resolution_verified_by FROM dbo.tbl_CS_serial
</cfquery>

<cfquery name="parts" datasource="CustomerSupport">
		SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
         FROM dbo.tbl_CS_parts

   
</cfquery>

Thank you,
Rach

[acoder]

You mentioned in the first post that you're able to get one table of information, but not three. When you get the table data using cfquery, you must be using it somewhere, e.g. cfoutput, cfloop to display it or otherwise make use of it. Also, what's the purpose of that form?

[bonneylake]

You mentioned in the first post that you're able to get one table of information, but not three. When you get the table data using cfquery, you must be using it somewhere, e.g. cfoutput, cfloop to display it or otherwise make use of it. Also, what's the purpose of that form?

Hey Acoder,

Ok here is how i do it.

On the index.cfm page i have this.

<cfquery name="ticket" datasource="CustomerSupport">
		SELECT pk_ticketID,status,title,date_last_modified,date_submitted,
customer_company FROM dbo.tbl_CS_ticketMaster
</cfquery>

<cfoutput query="ticket">
<a href="form/cticketpage1edit.cfm?pk_ticketID=#pk_ticketID#" target="_blank">#pk_ticketID#</a>
</cfoutput>

the index page basically allows the user to choose which ticket he is editing.

When you click on the link (which displays the ticket number the user is chooseing) it takes you to page 1 of the form that shows you the title of the ticket and contact information. When you click submit on the first page of the form it then takes you to the second page of the form where you see the contact information again (which it gets the contact information correctly) an then shows the serial and parts information (which is what i am trying to show). but reason we did it in 2 pages was because its a long form so didn't want to overwhelm the user.

Thank you,
Rach

[acoder]

If you're not going to use the queries on page 1, there's no need for them there. On page 2, you will need to make the modifications that you've made in page 1.

Alternatively, keep everything in page 1 and show only parts and allow other parts to be shown using JavaScript.

[bonneylake]

If you're not going to use the queries on page 1, there's no need for them there. On page 2, you will need to make the modifications that you've made in page 1.

Alternatively, keep everything in page 1 and show only parts and allow other parts to be shown using JavaScript.

Hey Acoder,

ok i am a bit confused. so basically take out the serial and part table and move it onto the second page? i am just confused on what needs to stay and what needs to go.

Heres whats on page 1

<cfquery name="ticket" datasource="CustomerSupport">
		SELECT pk_ticketID,title,priority,status,cost_center,fk_customer_number,
        customer_company,customer_Fname,customer_Lname,customer_add1,customer_city,customer_state,
        customer_zip,customer_email,customer_pri_phone,customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
        WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>

<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,software_hardware,resolution,resolution_date,
         verification_date,rma_data,type_hardware_failure,dept_responsibility,resolution_verified_by FROM dbo.tbl_CS_serial
          WHERE pkb_fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<cfquery name="parts" datasource="CustomerSupport">
		SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
         FROM dbo.tbl_CS_parts
         WHERE fk_ticketNo = #URL.pk_ticketID#
</cfquery>

<cfquery name="parts" datasource="CustomerSupport">
		SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,part_returned,defective,submission
         FROM dbo.tbl_CS_parts
</cfquery>
<cfoutput>
<form name="page1" id="page1" action="saveticket1edit.cfm?pk_ticketID=#pk_ticketID#"
method="POST" onSubmit="return validate_form();">
</cfoutput>

Thank you,
Rach

[acoder]

You can remove those queries from page 1 and put them in page 2.