# Join problem

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on

Hello,

I am trying to join together the following table in an attempt to find
the shortest route between all the coordinates.

+-----+-----+-------+
| frm | nxt | total |
+-----+-----+-------+
| 1-3 | 7-3 |    10 |
| 1-3 | 4-6 |    10 |
| 1-3 | 1-2 |     1 |
| 7-3 | 1-3 |    10 |
| 7-3 | 4-6 |    10 |
| 7-3 | 1-2 |     9 |
| 4-6 | 1-3 |    10 |
| 4-6 | 7-3 |    10 |
| 4-6 | 1-2 |     9 |
| 1-2 | 1-3 |     1 |
| 1-2 | 7-3 |     9 |
| 1-2 | 4-6 |     9 |
+-----+-----+-------+

I have been trying to INNER JOIN the a.nxt on b.frm as follows...

SELECT a.frm, a.nxt, b.nxt, c.nxt, (a.total+b.total+c.total) AS total
FROM tmp a
INNER JOIN tmp b ON a.nxt = b.frm
INNER JOIN tmp c ON b.nxt = C.frm
order by total;

I don't want any duplicates coordinates i.e.

from 1-2 -> 2-4 -> 1-2.

Can anyone help?

Cheers

## Re: Join problem

pingo wrote:

WHERE a.frm <> c.nxt

Regards,
Bill K.