Hello, I need help with a query.
I need to find the Sum of Distance of T# 1
But there isn't Adelaide -> Melbourne
So I'll have to take the value of Melbourne -> Adelaide which is 400
How can I handle this problem?

I can do them in a seperate query, but how do I get a sum of both?

Thanks!

``````select sum(dist)
from distance d, tripleg t
where (t.origin=d.origin and t.destination = d.destination)
and t#=1;

select sum(dist)
from distance dd, tripleg t
where (t.origin=dd.destination and t.destination = dd.origin)
and t#=1;``````
``````SQL> select * from tripleg;

T#       LEG# ORIGIN                         DESTINATION
-- ---------- ------------------------------ -----------
1          1 Sydney                         Melbourne
1          2 Melbourne                      Hobart
1          3 Hobart                         Perth
1          6 Melbourne                      Wollongong``````
``````SQL> select * from distance;

ORIGIN                         DESTINATION                          DIST
------------------------------ ------------------------------ ----------
Sydney                         Melbourne                            1000
Sydney                         Hobart                               1500
Sydney                         Perth                                2000
Sydney                         Brisbane                             1300
Sydney                         Wollongong                            100
Sydney                         Newcastle                             120
Melbourne                      Hobart                                500
Melbourne                      Perth                                2200
Melbourne                      Brisbane                             2300
Melbourne                      Wollongong                            950
Melbourne                      Newcastle                            1120
Hobart                         Perth                                2700
Hobart                         Brisbane                             2350
Hobart                         Wollongong                           1300
Hobart                         Newcastle                            1620
Perth                          Brisbane                             2500
Perth                          Wollongong                           2050
Perth                          Newcastle                            1980
Brisbane                       Wollongong                           1360
Brisbane                       Newcastle                            1800
Wollongong                     Newcastle                             120

28 rows selected.``````

You may use OR operator, Kindly note the positions of paranthesis.

``````SELECT SUM(dist)FROM distance dd, tripleg t
WHERE
( (t.origin=dd.destination AND t.destination = dd.origin) or  (t.origin=d.origin AND t.destination = d.destination) )

AND t#=1;``````

My query may cause you problem if there are two enteries for same route, â€¦

## All 2 Replies

You may use OR operator, Kindly note the positions of paranthesis.

``````SELECT SUM(dist)FROM distance dd, tripleg t
WHERE
( (t.origin=dd.destination AND t.destination = dd.origin) or  (t.origin=d.origin AND t.destination = d.destination) )

AND t#=1;``````

My query may cause you problem if there are two enteries for same route, I mean

I hope your table do not have such duplicate values

great urtrivedi,

nice R&D on above post..

and @andylbh
If you are not sure about duplicacy in your table as urtrivedi told in his post then you can use following query

``````SELECT SUM(dist)
FROM distance dd, tripleg t
WHERE
(((t.origin=dd.destination AND t.destination = dd.origin) and not (t.origin=d.origin AND t.destination = d.destination))
OR ((t.origin=d.origin AND t.destination = d.destination) and not (t.origin=dd.destination AND t.destination = dd.origin)))
AND t#=1;``````
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.