Sum + Sum of join table

[andylbh]

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?

Or if there's a different approach to this, please advise!
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          4 Perth                          Adelaide
 1          5 Adelaide                       Melbourne
 1          6 Melbourne                      Wollongong

SQL> select * from distance;

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

28 rows selected.

[urtrivedi]

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
Adelaide , Melbourne, 400
Melbourne , Adelaide , 400

I hope your table do not have such duplicate values

[pratik_garg]

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;