Skip to content
Advertisement

Joining 4 tables resulting an empty result

I have 4 tables with this detail on each table

deli_order Table

pg_order Table

pg_package Table

pg_send_package Table

I need to count transaction data that have either ‘cm_sign_time’ or ‘lp_sign_time’. The pg_package table need to be joined first to pg_order, after that it can be joined to deli_order table.

First I try to join the pg_send_package table with deli_order table first. This is my query

and the result

But when I join the other 2 table, the result i got is nothing. This is my query

This is the result

I need to sum the count result from transaction that have either ‘lp_sign_time’ or ‘cm_sign_time’ on it.

My expected result is

150 is came from 130 transactions with ‘lp_sign_time’ and 20 transactions with ‘cm_sign_time’ What should i change in my query?

Advertisement

Answer

You can try to use UNION ALL combine pg_send_package and pg_package table make a result set for order_code,code,lp_sign_time then do OUTER JOIN with COUNT.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement