QUERY HELP using two tables

[JJR]

Hey everyone,

 

kinda new to this sql stuff and need help building a query. I have two tables one called nodes and the other called cnodes.the nodes table stores information on the node and the cnode table contains  the 2 nodes that are connected. i need a query to go in to the cnode nodes check two conencted nodes and takes their number and then go in and take the lat and lng from nodes table and pass to vairables for a calculation.

 

how hard would this be? cos i have been working on it and cant gettin nothin to work

 

Thanks,

[fenway]

Could you post the table structure?  Sounds like you're going to need to join in the nodes table twice.

[JJR]

node table:

 

node, name, lat, lng

 

cnode table is:

 

node1, node2, distance

 

is that what you mean?

[fenway]

How do you figure out which cnode record to use/

[JJR]

should I add an id field?

 

I didnt explain the whole problem. the distance field in the cnode table is empty. I need the lat and lng for the node1 and node2 from the node table to put through a calculation algorithm and then put the answer in the distance field in the cnode field, but have no idea how to start.

 

could u help?

[fenway]

But the cnode table already has node1 and node2?

 

If so, then

 

select * from cnode as c 
inner join node as n1 on ( n1.node = c.node1)
inner join node as n2 on ( n2.node = c.node2)

 

Will get you all of the pairs -- it's not hard to convert this select statement into and update statement.

[JJR]

Hey im still lost.

 

should I not be creating a loop to go in and go through each row in the cnodes table....taking the value in node1 and the value in node2 looking up the node table and picking up the two sets of lat and lng. then passing these lat and lngs into lat1, lng1, lat2, lng2 and then doing the calculation.

 

maybe im not explaining it correctly.

 

JJR

[fenway]

Hey im still lost.

 

should I not be creating a loop to go in and go through each row in the cnodes table....taking the value in node1 and the value in node2 looking up the node table and picking up the two sets of lat and lng. then passing these lat and lngs into lat1, lng1, lat2, lng2 and then doing the calculation.

 

maybe im not explaining it correctly.

 

JJR

No need... a JOIN replaces this loop... and you can do the calcluation in mysql...