Tuesday, 22 October 2013

how to join multiple tables with left join using postgresql querry

select K.*,L.* from
(select I.*,J. columnname,J.columnnamefrom
(SELECT G.*,H.columnname,H.columnnameFROM
(SELECT E.*,F.columnname,F.columnnameFROM
(select c.*,d.columnname,D.columnnamefrom
 (
  select B.columnname,A.columnname,A.columnnamefrom tabbranchnames B
  LEFT JOIN
  (
    SELECT columnname,COUNT(A.columnname) AS INVOICENO,COALESCE(SUM(A.columnname),0) AS PAIDAMOUNT
    FROM tablename A,tablename B WHERE A.columnname= B.columnname
    AND CAST(B.columnname AS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
    AND  CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
    AND A.columnnameNOT LIKE 'RCQ%'  group by vchbranchname
   ) A on B.columnname=A.columnname
  )C
  LEFT JOIN
  (
     SELECT columnname,COUNT(A.columnname) AS preinvno,COALESCE(SUM(A.columnname),0) AS precash
     FROM tablename A,tablename B
     WHERE A.columnname= B.columnname
     AND CAST(B.columnname AS DATE) <'01-SEP-2013' AND CAST(B.DDC_LOGINDATE AS DATE) >= '21-JAN-2013' 
     AND  CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
     AND A.columnnameNOT LIKE 'RCQ%'
     group by columnname
  )D  on C.columnname=D.columnname
)E
LEFT JOIN
(
      SELECT b.columnname,COUNT(A.columnname) as chqinvoiceno,COALESCE(SUM(A.columnname),0) as chqamount
      FROM tablename A,tablename B,tablename C
      WHERE A.columnname= B.columnnameAND A.columnname= C.columnnameAND columnname= 'Y'
      AND  CAST(B.columnname AS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      AND C.columnname<= '30-SEP-2013' AND A.columnname LIKE 'RCQ%'
      group by columnname
)F
ON E.columnname=F.columnname) G
LEFT JOIN
(
SELECT columnname,COUNT(A.columnname) AS preinvoiceno,COALESCE(SUM(A.columnname),0) AS prechqamt
      FROM tablename A,tablename B,tablename C
      WHERE A.columnname= B.columnnameAND A.columnname= C.columnnameAND c.columnname= 'Y' 
      AND CAST(B.columnname AS DATE) <'01-SEP-2013' AND  CAST(B.columnname AS DATE) >= '21-JAN-2013' 
      AND C.columnnameBETWEEN '01-SEP-2013' AND '30-SEP-2013'
      AND A.columnnameLIKE 'RCQ%'
      group by columnname
) H on G.columnname=H.columnname) I
LEFT JOIN
(
SELECT B.columnname,COUNT(A.columnname) AS jvno,COALESCE(SUM(A.columnname),0) AS jvamt
      FROM tablename A,tablename B
      WHERE A.columnname= B.columnname
      AND CAST(B.columnname AS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      AND  CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      group by columnname
) J on I.columnname=J.columnname) K
LEFT JOIN
(SELECT B.columnname,COUNT(A.columnname) AS prvjvno,COALESCE(SUM(A.columnname),0) AS prvjvamt
      FROM tablename A,tablename B
      WHERE A.columnname= B.columnname
      AND CAST(B.columnname AS DATE) <'01-SEP-2013' AND CAST(B.DDC_LOGINDATE AS DATE) >= '21-JAN-2013'
      AND  CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      group by columnname) L on L.columnname=K.columnname

      where K.columnnameis not null or k.columnnameis not null or l.columnnameis not null
      or l.columnnameis not null or k.columnnameis not null or k.columnnameis not null or k.columnnameis not null or k.chqamount is not null
      or k.columnnameis not null or k.columnnameis not null or k.columnnameis not null or k.columnnameis not null

No comments:

Post a Comment