SQL Join w/ some Math thrown in

Question

Heck, maybe 'joining' isn't even involved. I'm way out of my sql league here. Could someone please help me out w/ the following:

Table A

 ItemId   ItemLookup   Price
 -------  ----------   -----
  1        123456       10.00
  2        234567        7.00
  3        345678        6.00

Table B

 ItemId   Location      Qty      QtyOnHold
 -------  ----------   -----     ---------
  1        1             26        20
  2        1             0         0
  3        1             12        6
  1        2             4         0
  2        2             2         1
  3        2             16        8

What I'm hoping to get is something that looks like

 ItemLookup, Price, (qty minus qtyonhold for loc1), (qty minus qtyonhold for loc2)
 or  123456, 10.00,         6,                              4

Thank you very much for any direction you can provide.

First try it without joining (ItemId instead of ItemLookup and Price). But you can't convert rows into columns using JOIN. — Marki555, May 07 '15 at 20:38

score 0 · Answer 1 · answered May 07 '15 at 20:29

You can use conditional aggregation and a join:

select a.ItemLookup,
       sum(case when Location = 1 then Qty - QtyOnHold end) as Location1,
       sum(case when Location = 2 then Qty - QtyOnHold end) as Location2
from tableb b join
     tablea a
     on b.ItemId = a.ItemId
group by a.ItemLookup;

score 0 · Answer 2 · answered May 07 '15 at 20:29

Somthing like this

select  tablea.* ,
(select (qty- QtyOnHold) as qty from tableb where ItemId = tablea.ItemId ans Location = 1 ) as qtyl1,
(select (qty- QtyOnHold) as qty from tableb where ItemId = tablea.ItemId ans Location = 2) as qtyl2
 from tablea

score 0 · Answer 3 · edited May 23 '17 at 11:43

0

This assumes that there's only one row in TableB for each ItemID + Location combination. This is basically just a "pivot", you can learn various ways to do this in MySQL here.

SELECT ItemLookup, Price, 
    MAX(IF(Location = 1, Qty-QtyOnHold, 0)) avail1,
    MAX(IF(Location = 2, Qty-QtyOnHold, 0)) avail2
FROM TableA AS a
JOIN TableB AS b ON a.ItemId = b.ItemId
GROUP BY a.ItemId

edited May 23 '17 at 11:43

Community

1
1

answered May 07 '15 at 20:30

Barmar

741,623
53
500
612

score 0 · Answer 4 · answered May 07 '15 at 21:55

It seems to me that it may be possible to have a variable number of locations for each item. If this is the case, you need an aggregate function to convert/concatenate multiple rows into a column. Here's an example with MySQL's group_concat function:

    select a.itemlookup,a.price,group_concat('loc ',location,'=',b.x order by location) as qty_minus_qtyonhold
    from tablea a,(select itemid,location,qty-qty_onhold x from tableb
    group by itemid,location) as b
    where a.itemid = b.itemid
    group by 1

You'll get a result like this:

 itemlookup   price  qty_minus_qtyonhold
 ----------   ------  ------------------
  123456      10.00   loc 1=6,loc 2=4
  234567      7.00    loc 1=0,loc 2=1
  345678      6.00    loc 1=6,loc 2=8

Not sure what DBMS you're using but there are similar alternatives for Oracle and SQL Server

SQL Join w/ some Math thrown in

4 Answers4