SQL help

alexh · #1 (**permalink**) 02-06-04, 06:36

I am trying to add the numeric values of several fields in two tables. If I perform the action on one table using the SQL below there is no problem.

Select SUM(cost)
as value
from actor
where actor_id = 1
or actor_id = 2
or actor_id = 3
or actor_id = 9

However when I try and add the extra table to the query I keep getting errors. Can anyone explain?

Select SUM(cost)
as value
from actor, director
where actor_id = 1
or actor_id = 2
or actor_id = 3
or actor_id = 9
or director_id = 1

reneeb · #2 (**permalink**) 02-06-04, 07:36

without any error-messages it is difficult to say what's going wrong. I assume there is the column cost in both tables. So you have to say from what table you want to get the cost.

Code:

Select SUM(actor.cost)
as value
from actor, director
where actor.actor_id = 1
or actor.actor_id = 2
or actor.actor_id = 3
or actor.actor_id = 9
or director.director_id = 1

alexh · #3 (**permalink**) 02-06-04, 07:46

Thanks for your reply, but the SQL does not work. I have some dummy data in there and the result should be 15,000 but the result from the query is 17235000. This must be multiplying each value instead off adding them.

Should I be using something other than SUM() maybe?

r937 · #4 (**permalink**) 02-06-04, 07:53

you are getting a cross join effect

you have not restricted which rows of the actor table should be matched up with which rows of the director table, so every row of the actor table is paired with every row of the director table, and then only those rows which meet the WHERE criteria are retained

i'm going to make a guess and say that you don't want a join, you want a union

what you probably want is something like this:

Code:

select sum(cost) as value
  from (
       select cost
         from actor
        where actor_id 
           in ( 1, 2, 3, 9 )
     union all
       select cost
         from director
        where director_id = 1
       )

unfortunately, mysql doesn't do UNION until 4.0, and doesn't do derived tables until 4.1

so you will probably have to run two queries:

Code:

select sum(cost) as value
  from actor
 where actor_id 
    in ( 1, 2, 3, 9 )
    
    
select sum(cost) as value
  from director
 where director_id = 1

and add up the two costs yourself

reneeb · #5 (**permalink**) 02-06-04, 07:58

is there a foreign key in one table (for example director_id in table actor)? Then you can do an inner join like this:

Code:

Select SUM(actor.cost)
as value
from actor inner join director on actor.director_id = director.director_id
where actor.actor_id in (1, 2, 3, 9)
or director.director_id = 1

alexh · #6 (**permalink**) 02-06-04, 08:53

Unfortunately there isn't a foreign key but thanks for your suggestion. I think I will just have to make two queries.

Internet Services

Web Development

Digital Marketing

Consumer Tech