0

I need to compile quantities from several warehouses that update their inventories periodically. The files are automatically loaded into tables in my MySQL database, but sometimes one warehouse might have a few more or less files than the others. I need to add them all to determine the total quantity available from the entire network.

My idea was to do: 

    SELECT
        IFNULL(branch_01.qty,0) +
        IFNULL(branch_02.qty,0) +
        IFNULL(branch_03.qty,0) +

etc. through all warehouses joined as:

    FROM branch_01 
        JOIN branch_02
        USING (oespaced)
        JOIN branch_03 
        USING (oespaced)

etc. through all warehouses

I can't use LEFT JOIN or RIGHT JOIN because sometimes one warehouse might have missing entries and sometimes another might. If a sku is missing from one branch's file, I'd prefer to still have the other branches added together, and just get a NULL, which would be converted to a 0 by the functions in the SELECT. When I've tested different joining solutions, I also seem to be getting Cartesian numbers of rows, which confuses me further. Any guidance is greatly appreciated.

Just a little clarification: We need to join 17 tables. We're not really concerned with the sum of a column, but more the individual values. For instance, a table might represent a list of items a,b,c,d, and list quantities of 1,2,3,4. We would have that table from several different warehouses and we would need to find a total for the entire network. If four warehouses had those values, we would want to see a,b,c,d with 4,8,12,16 as values for the total available.

user3326985
  • 1
  • 1
  • I understand why LEFT JOIN and RIGHT JOIN won't work for you - but did you try FULL OUTER JOIN? – PaF Feb 19 '14 at 08:40
  • I think a FULL OUTER JOIN would be a good solution, but from what I understand, MySQL doesn't support that, and it would be implemented by doing a LEFT JOIN on the left and a RIGHT JOIN on the right. – user3326985 Feb 19 '14 at 18:32
  • Thanks for the clarification - unfortunately, it's still very unclear. I think a specific example (table t1 has columns c1, c2 and values (v1a, v2a), (v1b, v2b), table t2 has columns... etc.) would help. Anyway, if the right solution is a FULL OUTER JOIN, and in MySQL you can't do it, then simulate it (as you mentioned yourself). – PaF Feb 19 '14 at 22:10

1 Answers1

0

I don't understand your question fully but hope my answer helps you a bit. Are you joining many tables? So let's say 2 tables and you want to sum up the quantity column?

First of all, JOIN performs the cartesian product of 2(or more) tables. So you'll get so many instances that you don't wish to have; the solution to this is using the WHERE.

Maybe this is what you are looking for:

SELECT sum(a.qty) + sum(b.qty)
FROM table1 a, table2 b
WHERE a.pk = b.fk -- this one resolves the unwanted instances

fk denotes foreign key and pk denotes primary key.

krato
  • 1,226
  • 4
  • 14
  • 30
  • Thank you very much for the suggestions @krato. I edited my original question to hopefully add a little clarification. The WHERE you suggested is probably the answer, but I don't know how to do it with our poor data, as one branch might only report a,b,c and another might only report b,c etc. If a branch doesn't report an item, we still need to know the values from the other branches instead of having them omitted from the results. – user3326985 Feb 19 '14 at 18:48