unmanaged data in union

Question

Forgive me if this question already ask,not much of db guy here , here is what i tried,

select row_number() over (partition by name order by challanto_date) , * 
from (
    select
        rma,
        p.id,
        p.name,
        challanto_date,
        CURRENT_TIMESTAMP as fromDate
    from challan_to_vendor cv
    left join challan_to_vendor_detail cvd on cv.id = cvd.challan_to_vendor_id 
    inner join main_product p on p.id = cvd.product_id
    union all
    select 
        rma, 
        p.id, 
        p.name, 
        CURRENT_TIMESTAMP as toDate, 
        challan_date
    from challan_from_vendor cv
    left join challan_from_vendor_detail cvd on cv.id = cvd.challan_from_vendor_id
    inner join main_product p on p.id = cvd.product_id
) as a

Here is my create table script :

challan_from_vendor

  CREATE TABLE public.challan_from_vendor
  (
  id character varying NOT NULL,
  date_ad date,
  rma integer DEFAULT 1,
  CONSTRAINT psk PRIMARY KEY (id)
  )

challan_from_vendor_detail

CREATE TABLE public.challan_from_vendor_detail
 (
 id character varying NOT NULL,
 challan_from_id character varying,
 product_id character varying,
 CONSTRAINT psks PRIMARY KEY (id),
 CONSTRAINT fsks FOREIGN KEY (challan_from_id)
     REFERENCES public.challan_from_vendor (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION
 )

challan_to_vendor;

CREATE TABLE public.challan_to_vendor
(
 id character varying NOT NULL,
 date_ad date,
 rma integer DEFAULT 1,
 CONSTRAINT pk PRIMARY KEY (id)
)

challan_to_vendor_detail

CREATE TABLE public.challan_to_vendor_detail
 (
 id character varying NOT NULL,
 challan_to_id character varying,
 product_id character varying,
 CONSTRAINT pks PRIMARY KEY (id),
 CONSTRAINT fks FOREIGN KEY (challan_to_id)
    REFERENCES public.challan_to_vendor (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
 )

product

    CREATE TABLE public.product
 (
 id character varying NOT NULL,
 product_name character varying,
 CONSTRAINT pks PRIMARY KEY (id)
 )

Here is my table structures and desire output.

challan_from_vendor

| id       | rma | date |
|:-----------|------------:|:------------:|
| 12012       |        0001 |     2018-11-10    
| 123121       |        0001 |     2018-11-11

challan_to_vendor

| id       | rma | date |
|:-----------|------------:|:------------:|
| 12       |        0001 |     2018-12-10    
| 123       |        0001 |     2018-12-11

challan_from_vendor_detail

 | id       | challan_from_vendor_id | product_id |
|:-----------|------------:|:------------:|
| 121       |  12012       | 121313        
| 1213       |   12012     |  131381

challan_to_vendor_detail

challan_from_vendor_detail

| id         | challan_to_vendor_id | product_id |
|:-----------|------------------------|:------------:|
| 121        |  12                    | 121313        
| 1213       |   123                  |  131381

product

  | id         | product_name | 
|:-----------|------------:|
| 191313       |    apple   |
| 89113       |      banana |

Output

| ram         | product_id | challan_from_date | challan_to_date|
|:-----------|------------:|:-----------------:|:--------------:|
| 0001       |       191313|     2018-11-10    |2018-11-11      |     
| 0001       |       89113 |     2018-12-10    |2018-12-11      |     

Answer 1

I think best of it is still to came , but i feel comfortable with **full join **.

SELECT
a1.ram, 
a1.product_id,
a1.date_ad as fromDate,
a2.date_ad as toDate  
FROM
 (
   select 
    ram,date_ad, cvd.product_id
from challan_from_vendor cv left join challan_from_vendor_detail cvd on cv.id = cvd.challan_from_id   
group by 1,date_ad, cvd.product_id
) as a1
FULL JOIN 
(
 select 
    ram, date_ad, cvd.product_id
from challan_to_vendor cv left join challan_to_vendor_detail cvd on cv.id = cvd.challan_to_id     
group by 1, date_ad,cvd.product_id
) as a2 ON (a1.product_id=a2.product_id)
ORDER BY 1;

Answer 2

--Please try this Query

SELECT fv.rma, p.id, fv.date_ad, tv.date_ad
FROM challan_from_vendor FV
INNER JOIN challan_from_vendor_detail FVD ON FVD.challan_from_id = fv.id
INNER JOIN product P ON P.id = FVD.product_id
INNER JOIN challan_to_vendor tv ON tv.rma = fv.rma
INNER JOIN challan_to_vendor_detail tvd ON tvd.challan_to_id = tv.id AND tvd.product_id = fvd.product_id