Results 1 to 6 of 6

Thread: SQL help

  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: SQL help

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    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
    board.perl-community.de - The German Perl-Community

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    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
    board.perl-community.de - The German Perl-Community

  6. #6
    Join Date
    Feb 2004
    Posts
    6
    Unfortunately there isn't a foreign key but thanks for your suggestion. I think I will just have to make two queries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •