Discussions

Expand all | Collapse all

join issue between tables

Jump to Best Answer
  • 1.  join issue between tables

    Posted 10-18-2019 03:38
    Edited by Nagulan Santhi 6 days ago
    i have three queries data is same but values is getting different from each:



    now here i have combined first two queries so i should result as 144104.96 ,  229865.06
    but i am getting result as  29685621.76|13332173.48
    i guess this is due to join values is getting repeated for each join 

    how can i combine first two so that i can get same result (is there any way to query from different tables)
    #Core
    #Immerse


  • 2.  RE: join issue between tables

    Posted 10-18-2019 06:34
    Hi @Nagulan Santhi,

    Probably because of the cardinality of the records before the sum is increasing, and you are going to sum a lot of duplicates values from both the tables.
    A product's likely to have more than one part, so the duplicates.

    Can you rewrite the query like this, and check if you are getting the right results
    select  revenue.m_num,
            "label_PO_ITEM.totalcost", 
            "label_SO_ITEM.totalprice" 
    from    (select m_num, CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) AS "label_PO_ITEM.totalcost"
             from "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id
             where "m5d9f6525_part".m_num = '200124'
             group by m_num) as cost
             join
             (select m_num, CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) AS "label_SO_ITEM.totalprice" 
             from "m5d9f6525_SO_ITEM" JOIN m5d9f6525_product ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id 
             WHERE "m5d9f6525_product".m_num = '200124'
             group by m_num) as revenue
             on cost.m_num=revenue.m_num
             LIMIT 1001;​



  • 3.  RE: join issue between tables

    Posted 10-18-2019 07:13
    Hi @Candido Dessanti
              Thanks a lot its working fine
              can we query this without using subquery 



  • 4.  RE: join issue between tables

    Posted 10-18-2019 07:59
    Edited by Candido Dessanti 10-18-2019 08:01
    Hi @Nagulan Santhi,

    we could use window functions, but it can make some approximations and the performance could be worse.

    If you need to query just each time, you can remove the query condition using a cross join.

    To improve the readability of you can use with clause

    with cost as ((select m_num, CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) AS "label_PO_ITEM.totalcost"
             from "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id
             where "m5d9f6525_part".m_num = '200124'
             group by m_num),
    revenue as (select m_num, CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) AS "label_SO_ITEM.totalprice" 
             from "m5d9f6525_SO_ITEM" JOIN m5d9f6525_product ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id 
             WHERE "m5d9f6525_product".m_num = '200124'
             group by m_num)
    select  revenue.m_num,
            "label_PO_ITEM.totalcost", 
            "label_SO_ITEM.totalprice" 
    from     cost join revenue
             on cost.m_num=revenue.m_num
             LIMIT 1001;​​




  • 5.  RE: join issue between tables

    Posted 10-21-2019 01:15
    Hi @Candido Dessanti
     
    Thanks for your effort but is there any way to use any other joins with a single query. because for sub query it needs a lot of effort to redesign in coding. so can you help me out of this problem​


  • 6.  RE: join issue between tables

    Posted 10-21-2019 02:15
    Edited by Candido Dessanti 10-21-2019 02:16
    HI @Nagulan Santhi,
    product
    the problem is about cardinality of data on different tables you are trying to join, so if in the product table we have 2 records for the product (sales) and 3 records for the item (costs), joining them would produce 6 row the group by, so the sum of sales going to be 3 times the real value while the costs 2 times

    try this
    SELECT CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) / count(distinct  "m5d9f6525_SO_ITEM".rownum) AS "label_PO_ITEM.totalcost",
           CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / count(distinct  "m5d9f6525_PO_ITEM".rownum) AS "label_SO_ITEM.totalprice" 
    FROM "m5d9f6525_PO_ITEM" 
    JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id 
    JOIN m5d9f6525_product ON "m5d9f6525_part".m_num = "m5d9f6525_product".m_product_short_num 
    JOIN m5d9f6525_SO_ITEM ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' LIMIT 1001​







  • 7.  RE: join issue between tables

    Posted 10-21-2019 03:30
    Edited by Nagulan Santhi 10-21-2019 03:31
    Hi @Candido Dessanti

    i am getting error as no column as rownum in "m5d9f6526_SO_ITEM"
    how to use rownum function in mapd


  • 8.  RE: join issue between tables
    Best Answer

    Posted 10-21-2019 03:34
    Ops...I used of rowid. The correct query would be this one

    SELECT CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) / count(distinct  "m5d9f6525_SO_ITEM".rowid) AS "label_PO_ITEM.totalcost",
           CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / count(distinct  "m5d9f6525_PO_ITEM".rowid) AS "label_SO_ITEM.totalprice" 
    FROM "m5d9f6525_PO_ITEM" 
    JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id 
    JOIN m5d9f6525_product ON "m5d9f6525_part".m_num = "m5d9f6525_product".m_product_short_num 
    JOIN m5d9f6525_SO_ITEM ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' LIMIT 1001​​



  • 9.  RE: join issue between tables

    Posted 10-21-2019 04:17
    Edited by Nagulan Santhi 10-21-2019 04:17
    Hi @Candido Dessanti
    ​​  Its working fine, Thanks for your idea and effort 
      but if i had about five to six tables can you suggest whether i can use this method


  • 10.  RE: join issue between tables

    Posted 30 days ago
    Hi @Nagulan Santhi,

    the basic principle is to divide the summed value (this is going to work for sum only of course) by the total number of rows of divided by the resulting rows table.

    so have three tables you are going to join and the cardinality is 2, 3 and 5 you have to divide the count(*) with the count distinct of that column.

    so sum(table1)/(count(*)/count(distinct table1.rowid)),
    sum(table2)/(count(*)/count(distinct table2.rowid)),
    sum(table3)/(count(*)/count(distinct table3.rowid).

    rewriting your query
    SELECT CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) / (count(*)/count(distinct  "m5d9f6525_PO_ITEM".rowid)) AS "label_PO_ITEM.totalcost",
           CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / (count(*)/count(distinct  "m5d9f6525_SO_ITEM".rowid))  AS "label_SO_ITEM.totalprice" 
    FROM "m5d9f6525_PO_ITEM" 
    JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id 
    JOIN m5d9f6525_product ON "m5d9f6525_part".m_num = "m5d9f6525_product".m_product_short_num 
    JOIN m5d9f6525_SO_ITEM ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' LIMIT 1001​​
    
    
    ​

    I haven't tried, but it should work



  • 11.  RE: join issue between tables

    Posted 30 days ago
    Edited by Nagulan Santhi 6 days ago
    Hi @Candido Dessanti



    in this query we have only two tables now i have tried / (count(distinct "m5dad587e_product".rowid)  for label_product.cost but its giving worng result
    i think this can be applied only if we have three tables in join, if we have two tables its getting wrong result so how to query this to avoid cardinality of the records


  • 12.  RE: join issue between tables

    Posted 30 days ago
    Hi,
    You have to divide by (count(*) /count(distinct rowid of table where the measure is coming)).


  • 13.  RE: join issue between tables

    Posted 30 days ago
    Edited by Nagulan Santhi 6 days ago
    Hi @Candido Dessanti


    its giving 31.5 as cost


  • 14.  RE: join issue between tables

    Posted 30 days ago
    Edited by Nagulan Santhi 30 days ago
    Hi @Candido Dessanti
    i have done count(*)/count(distinct rowid) but result in getting differ in above query can you please check


  • 15.  RE: join issue between tables

    Posted 30 days ago
    Hi @Nagulan Santhi,

    The count(*)/count(distinct ...) is needed when you need to sum some data ignoring the duplication due from the joins, but in this particular case you need to sum duplicated data, so you divide the summed data by anything.



  • 16.  RE: join issue between tables

    Posted 30 days ago
    so how can we do for this case


  • 17.  RE: join issue between tables

    Posted 30 days ago
    If you need to sum duplicated data, you should just do the sum without any division. 

    btw aware about your data, and what you want to achieve with all you queries


  • 18.  RE: join issue between tables

    Posted 29 days ago
    Hi @Candido Dessanti
     thanks for your effort and time
    can you explain rowid function in mapd because

    SELECT count(distinct m5dad587e_product.rowid) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'

    i am getting count as 1

    SELECT count(*) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'​​

    i am getting count as 7 

    i have checked all row as unique but i am getting rowid count as 1


  • 19.  RE: join issue between tables

    Posted 29 days ago

    Hi @Nagulan Santhi,

    In Omnisci, like other databases, rowid isn't a function but a pseudo column; each row you insert in a table has a unique rowid. On omnisci is a progressive number starting from 1 up to the number of rows in the table.

    I used rowid because I needed a unique identifier of the record in a particular table, without the need to know anything about that table, to spot, using the distinct clause, how many rows were coming from that table before the join.

    In case you have unwanted duplication due to the join of two tables with different cardinality for the key used in the join, dividing the count() by the count(distinct rowid), return how many times that row got duplicated, so dividing the sum by this number, return the value you would get without the join.




  • 20.  RE: join issue between tables

    Posted 29 days ago
    Hi @Candido Dessanti
     In mapd rowid is unique number which has for all rows including duplicate rows but why i am getting count(*) as 7 and count(rowid) as 1
    for below queries

    SELECT count(distinct m5dad587e_product.rowid) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'

    i am getting count as 1

    SELECT count(*) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'​​

    i am getting count as 7 

    in above all were same but count(*) value is differing from count(rowid)



  • 21.  RE: join issue between tables

    Posted 29 days ago
    Hi @Nagulan Santhi,

    the values are changing because of the count distinct of a column will count the distinct values present in a group/table, regardless of the number of rows in the group, while the count(*) is going to return the number of rows in that group regardless of the contents of the columns in the rows.

    using you query without grouping

    SELECT m5dad587e_product.rowid AS product_rowid,
           m5dad587e_product".m_cost as product_cost,
           "m5dad587e_purchase_order.rowid" as order_rowid
    FROM   "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id 
    WHERE  "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' 
    AND    "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'​

    will return

    product_rowid|product_cost|order_rowid
    476          |25          |102
    476          |25          |134
    476          |25          |153
    476          |25          |167
    476          |25          |178
    476          |25          |320

    Asking a count distinct of product_rowid, the database will return the number of distinct values on that row; in this case all of them are 476, so it will return 1
    while asking a count(*) it will return the number of rows in the resultset, so 7.


  • 22.  RE: join issue between tables

    Posted 29 days ago
    yes i got it thanks a lot for your time and effort @Candido Dessanti


  • 23.  RE: join issue between tables

    Posted 28 days ago
    Edited by Nagulan Santhi 28 days ago
    Hi @Candido Dessanti

    one last help sorry to disturb you again and again  in same context

    SELECT distinct(CAST(sum(coalesce(subquery."label_SO_ITEM.totalprice", 0)) AS DOUBLE PRECISION)) AS "label_SO_ITEM.totalprice" FROM (SELECT distinct(CAST(sum(coalesce(round("m5dac68ce_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / (count(*) / count(distinct(m5dac68ce_SO_ITEM.rowid)))) AS "label_SO_ITEM.totalprice" FROM m5dac68ce_SO_ITEM WHERE "m5dac68ce_SO_ITEM".m_datelastfulfillment BETWEEN '2020-01-01' AND '2020-12-31') AS subquery;

    Exception: Division by zero​
    i am getting this issue if there is no records in a table is there way to fix this


  • 24.  RE: join issue between tables

    Posted 28 days ago
    Edited by Archi K 28 days ago
    If there is no records in the table then probably count() returns 0 so that You have division by 0 here:

    SELECT distinct(CAST(sum(coalesce(subquery."label_SO_ITEM.totalprice", 0)) AS DOUBLE PRECISION)) AS "label_SO_ITEM.totalprice" FROM (SELECT distinct(CAST(sum(coalesce(round("m5dac68ce_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / (count(*) / count(distinct(m5dac68ce_SO_ITEM.rowid)))) AS "label_SO_ITEM.totalprice" FROM m5dac68ce_SO_ITEM WHERE "m5dac68ce_SO_ITEM".m_datelastfulfillment BETWEEN '2020-01-01' AND '2020-12-31') AS subquery;

    You have to check if any of this part does not return 0 records and then do the division.


  • 25.  RE: join issue between tables

    Posted 28 days ago
    Edited by Candido Dessanti 27 days ago
    Hi @Nagulan Santhi,

    As @Archi K said the problem arise when the any of the counts is 0 you will get that exception

    to fix just change the expression in 

    (when count(*) = 0 then 1 else count(*) end / when count(distinct(m5dac68ce_SO_ITEM.rowid)) = 0 then count(distinct(m5dac68ce_SO_ITEM.rowid)) end)​


    or you can set the parameter null-div-by-zero to true. check you omnisci.conf file


    ​​In this particular case, where no joins are happening it's sufficient to sum the results with divide by anything; same results, better performances