Search code examples
sqlfirebird

How to fix a query that produces too many rows?

I'm designing Firebird 3.0 database for service sales, for example, for beauty saloons etc.

The database has the tables:

Serv - for service's list;

ServRecs - for service sales records;

Docs - for service documents;

Calc - for service calculations i.e. which raw material is used in specific service, quantity of raw material etc;

RecsOut - for raw material output records (sales);

RecsIn - for raw material Input records;

Inventory - for raw material's & good's names and properties.

Serv: Id, name, qnt, Vat...

ServRecs: Id, serv_id, Doc_id, qnt...

Docs: doc_id, docN, DocDT, Summ, ...

Calc: Id, serv_id, RawMat_id, qnt, unit_id...

RecsOut: id, doc_id, good_id, RecsIn_id

RecsIn: id, good_id...

Inventory: id, name (Rawaterial's and good's name)...

Let me explain with an example:
There is service document 323. There are 2 services used in it: service with serv_id=7 (hair cutting) and serv_id=8 (hair washing). As ServRecs table's qnt field shows service with serv_id=8 is used 2 times (i.e. 2 washing, before and after coloring), service with serv_id=7 only 1 time. As Calc table shows, generally, on service #7 are used raw material with id=11446 15ml and with id=11448 15ml, on service #8 - raw material with id=11450 10ml. That is, total used: raw material 11446 - 15ml, 11448 - 15ml and 11450 - 20ml (2*10ml).

enter image description here

My query looks like this:

select
i.id,
i.name as UsedRawMaterialName,
s.name as ServiceName,
ro.doc_id as ServiceDoc_id,
ri.cost as CostofRawMaterial,
sr.qnt as ServiceQnt,   --used service quantity, for example, 2 times washing 
sr.qnt*c.qnt as UsedRawMaterialQnt, --used service quantity*rawmaterial's used for 1 service
i.unit_k 

 from Inventory I, RecsOut ro, RecsIn ri,  calc c,  servrecs sr, serv s, Docs d, unit u,
  where
 d.doc_id= ro.doc_id and d.doc_id=sr.doc_id and d.doc_id=323  and
 s.id=c.serv_id and sr.serv_id=c.serv_id and
 c.rawmat_id=i.id and
 ro.recsIn_id=ri.id and
 i.unit_k=u.unit_k

My aim is get result like this:

enter image description here

However, the query returns result with redundant records and wrong values like this:

enter image description here

What is wrong in my query?

Update 1: I changed "old-style Join syntax" with "new-style Join syntax" and easily find out that error was in "Join RecsOut ro on ro.id=i.id" clause. "New-style Join" is really very visually informative than old-style.

select
 i.id,
 i.name as UsedRawMaterialName,
 s.name as ServiceName,
 ro.doc_id as ServiceDoc_id,
 ri.cost as CostofRawMaterial,
 sr.qnt as ServiceQnt,   --used service quantity, for example, 2 times washing 
 sr.qnt*c.qnt as UsedRawMaterialQnt, --used service quantity*rawmaterial's used for 1 service
 i.unit_k 
        
 from  
 Inventory I Join RecsOut ro on ro.id=i.id
             Join RecsIn ri on ro.recsin_id=ri.id 
             Join calc c on c.rawmat_id=i.id
             join ServRecs sr on sr.serv_id=c.serv_id
             Join serv s on s.id=c.serv_id
             Join doc d on d.doc_id=ro.doc_id and 
                           d.doc_id=sr.doc_id and 
                           d.doc_id=323 
             join unit u on i.unit_k=u.unit_k
Solution

  • @basti A major benefit of the "New Style Join" is that each table can be brought in one at a time during development and testing. With each table "joined" it is very straightforward to see which relationship has generated more (or indeed less) records than you are expecting

    Translating your code shows me there could be breakage somewhere . Thanks for replying to comment ...

    from Inventory I
  join RecsOut ro on ro.recsIn_id=ri.id 
 -- ??? join RecsIn ri, --- ?? 
  join calc c on c.rawmat_id=i.id
  join servrecs sr on sr.serv_id=c.serv_id 
  join serv s on s.id=c.serv_id 
  join Docs d on d.doc_id= ro.doc_id 
      and d.doc_id=sr.doc_id
      and d.doc_id=323 
  join unit u on i.unit_k=u.unit_k

    Don't forget to embrace inner , left and outer joins