November 20th, 2012, 02:35 PM
Full outer join on 4 tables
I have 4 tables which are in basically the same structure
| key1 | id | avg1 | count1 |
| key2 | id | avg2 | count2 |
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:
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.
November 21st, 2012, 02:33 AM
November 21st, 2012, 02:48 AM
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.
November 21st, 2012, 02:52 AM
November 21st, 2012, 03:23 AM
SELECT 'table1' tbl...
SELECT 'table2' ...
November 21st, 2012, 06:21 AM
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?
November 21st, 2012, 07:49 AM
Yes. There's a much, much better way, but you specifically asked us not to mention it.