CFQuery - Whats wrong with my code ??

[kingjjx]

Hi, Im wondering whats wrong with my code ...
If I use this 1st set of code below .. it gives me the right results ....

<cfquery name=&quot;shit&quot; datasource=&quot;EncoreCompanyD&quot;>
SELECT ArCustomer.Customer, ArCustomer.Name
FROM dbo.ArCustomer
WHERE (ArCustomer.Customer = '0013746')
</cfquery>


****** BUT if I use this below, it gives me bad query results. This is querying 2 tables from different databases. There is only 1 customer 0013746 but with this code below, it returns a bunch of 0013746 customers.


<cfquery name=&quot;shit&quot; datasource=&quot;EncoreCompanyD&quot;>
SELECT ArCustomer.Customer, ArCustomer.Name, p.name
FROM dbo.ArCustomer, DynoOwnerRec.dbo.TuningLinkIssues p
WHERE (ArCustomer.Customer = '0013746')
</cfquery>


What is wrong with the 2nd code ???
thank you

 

[profwannabe]

What is the relationship between the two tables? I think you need to include a where clause like ArCustomer.Customer = p.Customer. Alternatively, use a Join statement (which will also require that you stipulate the connection between the tables).

 

[kingjjx]

How do I do the join statement ?

Basically, the ArCustomer table contains the customer name and number and the rest of the info i need is in the TuningLinkIssues table.

 

[Sarky78]

a join statement is along the lines of:

SELECT whatever
FROM tableA INNER JOIN tableB ON tableA.UniqueKey=tableB.MatchingKey

hope this helps !

 

[profwannabe]

Two options:

1. SELECT ArCustomer.Customer, ArCustomer.Name, p.name
FROM dbo.ArCustomer, DynoOwnerRec.dbo.TuningLinkIssues p
WHERE p.Customer = dbo.ArCustomer.Customer
AND (ArCustomer.Customer = '0013746')

OR

2.SELECT ArCustomer.Customer, ArCustomer.Name, p.name
FROM dbo.ArCustomer INNER JOIN dbo.TuningLinkIssues p ON p.Customer = dbo.ArCustomer.Customer
WHERE (ArCustomer.Customer = '0013746')

Both do the same thing, just the join in the first is in the WHERE clause while in the second it is in the FROM clause. There are various kinds of joins so the second gives more flexibility if you want, for example, all the records from one table and only matching ones from another...

 

[kingjjx]

hi, what if table A and table B does not have a matching key ?

what im trying to do is get the customer number from table A and input it in table B.

so, what I am trying to do is query table A, then add an EDIT button with the query result , then in the EDIT page, once the user clicks submit, it puts the info to Table B.

link is:

http://dynamic.dynojet.com/cfml/tuninglink/search_tl.cfm

type in: ADVANCED

is what im tryin to do possible ?
thanks

 

[profwannabe]

Hmm,

Are you trying to create a table with an edit function for each record? If so, you need to create a form for each record in which you pass the relevant information when the form is submitted. You can do this easily enough with a cfloop.

Is that what you have in mind?

 

[kingjjx]

hey, thanks guys .. i finally figured out how to solve the problem.

i just queried the 1st table, took the values that i needed then inserted it to the 2nd table.

thanks !!