1toMany Records

[meridian99]

I would like to create a sql that provides a single row subquery to search 2 tables. 1 being a master linked to a details which contains 1/more transaction records for each master.

for each unique piece of equipment, total up all its costs for a given year as in

pump12345, main motor pump, $25,700.00

Something like...

select equipment_no, equipment_desc
from equipment_table
where equipment_status = 'ACTIVE' and year = 2004 and
total_cost = (select sum(costs) from equip_data);


equip_no is in both tables with the costs in the detail
table.

Not sure if I can do the SUM() and have it passed back to the main query for reporting?

 

[SantaMufasa]

Meridian,

Here is code for you:

Section 1 -- Confirmation of table contents:

col a heading "Equipment|Number" format a10
col b heading "Equipment|Description" format a15
col c heading "Equipment|Status" format a9
select equipment_no a, equipment_desc b, equipment_status c from equipment_table;

Equipment  Equipment       Equipment
Number     Description     Status
---------- --------------- ---------
pump12345  main motor pump ACTIVE

select * from equip_data;

EQUIPMENT_NO         YEAR      COSTS
-------------------- ---- ----------
pump12345            2004      14700
pump12345            2004      10700

Section 2 -- Code to join these two tables:

col c heading "Total|Costs|for|2004" format $999,999.99
select e.equipment_no a, e.equipment_desc b, c.total_cost c
from equipment_table e
 ,(select equipment_no, year, sum(costs) total_cost
  from equip_data
  group by equipment_no,year) c
where  e.equipment_status = 'ACTIVE'
  and e.equipment_no = c.equipment_no
  and c.year = 2004
/

                                  Total
                                  Costs
Equipment  Equipment                for
Number     Description             2004
---------- --------------- ------------
pump12345  main motor pump   $25,400.00

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:50 (09Aug04) UTC (aka "GMT" and "Zulu"), 14:50 (09Aug04) Mountain Time)

 

[dbtoo2001]

Try analytics?

select equipment_no, equipment_desc, sum(costs) over (partition by equipment_no)
from equipment_table a, equip_data b
where equipment_status = 'ACTIVE' and year = 2004 and
a.equipment_no = b.equipment_no;

-- the 'year = 2004' doesn't look like this is 'real' sql.