#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3

    Full outer join on 4 tables


    Hi,

    I have 4 tables which are in basically the same structure

    table1:
    | key1 | id | avg1 | count1 |

    table2:
    | key2 | id | avg2 | count2 |

    etc.

    many of the ids overlap, so that table1.id=table2.id=table3.id=table4.id, but some of the ids are unique and some are only shared between 2 or 3 of the tables.
    I am trying to get a full list of distinct ids with weighted averages
    i'm trying to do:
    select
    id, (weight1+weight2+weight3+weight4) / (count1+count2+count3+count4) as weightedavg
    from
    (select id, (avg1*count1) as weight1 from table1) as faketable1
    full outer join
    (select id, (avg2*count2) as weight2 from table2) as faketable2
    full outer join
    (select id, (avg3*count3) as weight3 from table3) as faketable3
    full outer join
    (select id, (avg4*count4) as weight4 from table4) as faketable4
    the issue is of course that i'm missing the "on faketable1.id=faketable2.id", which i can't really do anyways since some ids are distinct

    from what i've found online, mysql does not really have full outer joins, and even if it did, i'm not sure what i could join all 4 tables on. any ideas?


    I appreciate any responses, but please do not tell me to change the structure of the tables, since I do not have access to do that.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Union?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    i've been trying to figure out how to do this with union but how would i distinguish between the values from each table for the count?

    sample query please.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    would this work?

    select id, sum(tweight)/sum(tcount), sum(tcount)
    from
    (select id, (avg1*count1) as tweight, count1 as tcount from table1
    union all
    select id, (avg2*count2) as tweight, count2 as tcount from table2
    union all
    select id, (avg3*count3) as tweight, count3 as tcount from table3
    union all
    select id, (avg4*count4) as tweight, count4 as tcount from table4) tableall
    group by id
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    SELECT 'table1' tbl...
    UNION
    SELECT 'table2' ...

    ?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    a basic union would exclude duplicate values which i would need for the group by. i've tried it, and my query seems to work, but it is very heavy. is there an easier/cleaner/faster way to do this?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Yes. There's a much, much better way, but you specifically asked us not to mention it.

IMN logo majestic logo threadwatch logo seochat tools logo