UNION ALL WITH WHERE CLAUSE

the basic "syntax" for union is:

select ... from ... where ...
UNION
select ... from ... where ... 

Select allOpen in new window

etc.

YES BUT ITS ASKING ME TO PUT VALUE

 select * from ( Select BD.CST as MemoNo, BD.GRDate as M_Date, BD.BiltyNo as LR_No, BD.GRNo as Cros_No, BD.Total as Vasuli, BD.pf as Crossing, BD.Total - BD.pf as Balance, 0 as Amt_Rcvd, BD.Consignee from Bilty_Detail UNION ALL SELECT 0 as RcptNo, Receipt_CLNT.Rcpt_Date, 0 as Amt_Rcvd, Receipt_CLNT.RcptNo, 0 as Total, 0 as pf, 0 as Balance, Receipt_CLNT.Amt_Rcvd, Receipt_CLNT.G_Name from Receipt_CLNT
ORDER BY Consignee, MemoNo, LR_No;) as UnionTable inner join 
 thirdtable on UnionTable .CONSIGNEE =thirdtable.CONSIGNEE

Select allOpen in new window

sorry bring order by to the end of query:-

select  * from ( Select BD.CST as MemoNo, BD.GRDate as M_Date, BD.BiltyNo as LR_No, BD.GRNo as Cros_No, BD.Total as Vasuli, BD.pf as Crossing, BD.Total - BD.pf as Balance, 0 as Amt_Rcvd, BD.Consignee from Bilty_Detail UNION ALL SELECT 0 as RcptNo, Receipt_CLNT.Rcpt_Date, 0 as Amt_Rcvd, Receipt_CLNT.RcptNo, 0 as Total, 0 as pf, 0 as Balance, Receipt_CLNT.Amt_Rcvd, Receipt_CLNT.G_Name from Receipt_CLNT
) as UnionTable inner join 
 thirdtable on UnionTable.CONSIGNEE =thirdtable.CONSIGNEE ORDER BY UnionTable.Consignee, UnionTable.MemoNo, UnionTable.LR_No;

Select allOpen in new window

better:-

select UnionTable. * from ( Select BD.CST as MemoNo, BD.GRDate as M_Date, BD.BiltyNo as LR_No, BD.GRNo as Cros_No, BD.Total as Vasuli, BD.pf as Crossing, BD.Total - BD.pf as Balance, 0 as Amt_Rcvd, BD.Consignee from Bilty_Detail UNION ALL SELECT 0 as RcptNo, Receipt_CLNT.Rcpt_Date, 0 as Amt_Rcvd, Receipt_CLNT.RcptNo, 0 as Total, 0 as pf, 0 as Balance, Receipt_CLNT.Amt_Rcvd, Receipt_CLNT.G_Name from Receipt_CLNT
) as UnionTable inner join 
 thirdtable on UnionTable.CONSIGNEE=thirdtable.CONSIGNEE ORDER BY UnionTable.Consignee, UnionTable.MemoNo, UnionTable.LR_No;

Select allOpen in new window

not working
third table name is ClientLedger

database is Access 2003 (Not SQL Server)

Select BD.CST as MemoNo, BD.GRDate as M_Date, BD.BiltyNo as LR_No, BD.GRNo as Cros_No, BD.Total as Vasuli, BD.pf as Crossing, BD.Total - BD.pf as Balance, 0 as Amt_Rcvd, BD.Consignee 
from Bilty_Detail BD INNER JOIN thirdtable ON (thirdtable.CONSIGNEE = BD.CONSIGNEE )
UNION ALL 
SELECT 0 as RcptNo, Receipt_CLNT.Rcpt_Date, 0 as Amt_Rcvd, Receipt_CLNT.RcptNo, 0 as Total, 0 as pf, 0 as Balance, Receipt_CLNT.Amt_Rcvd, Receipt_CLNT.G_Name 
from Receipt_CLNT
ORDER BY Consignee, MemoNo, LR_No;

Select allOpen in new window

@angelIII:
Yes thats i wanted
Let me try

Point increased :=)

Can you please show me the query joining with four table.

Table Names
table1 name is CM
Table2 name is BD
Table3 name is RcptCLNT
Table4 name is CLedger

Fields Name
CM.Cmdate
CM.CmNo
CM.CmAmt
CM.Cnee

BD.BDate
BD.LRno
BD.MemoNo
BD.MDate
BD.Cnee

RcptCLNT.Rcpt_Date
RcptCLNT.RcptAmt
RcptCLNT.ChqDdNo
RcptCLNT.ChqDdDate
RcptCLNT.Type_P
RcptCLNT.RcptNo
RcptCLNT.Cnee

CLedger.Cnee

and sequence is like this

CM.Cmdate, cm.CmNo, BD.BDate, BD.LRno, CM.CmAmt, RcptCLNT.RcptAmt, RcptCLNT.ChqDdNo, RcptCLNT.ChqDdDate, RcptCLNT.Type_P
RcptCLNT.RcptNo, BD.MemoNo, BD.MDate, RcptCLNT.Rcpt_Date

Please Note: RcptCLNT.Rcpt_Date and CM.Cmdate is on Same Column and rest of the fields are on independent columns

and INNER JOIN CLedger ON (CLedger.CNee = CM.Cnee)

all table have one common field is Cnee.

what query will be ...??

Thanking you

@angellll: Please check my previous Comment,
your answer is acceptable but i need some amendment, some fields need to be removed and some new field and new table will be add,
so please check my previous comment
after answer i will go for more points ;-)
 

well,the question is: you know what fields to add/remove, so why do you ask me to change the query in that regards?
what is the issue you have?

ok i tried but no luck,
it says "The Number of columns in the two selected tables or queries of a union query do not match

Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD 
INNER JOIN Receipt_CLNT CM ON (CM.CNee = BD.Consignee))
UNION ALL SELECT CM.CM_Date AS Cdate, CM.Cash_MemoNo AS CmNo,0 as BookDate, 0 as LR_No, CM.CM_Total as CMAmt, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,0 as MemoNo, 0 as M_Date 
from (CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))
UNION ALL SELECT Receipt_CLNT.Rcpt_Date, 0 as CmNo, 0 as BookDate, 0 as LR_No, 0 as CM_Total, Receipt_CLNT.Amt_Rcvd as RcptAmt,Receipt_CLNT.ChqDDNo as RefNo, Receipt_CLNT.ChqDDdate as RefDate, Receipt_CLNT.Amt_Mode as Mode, Receipt_CLNT.RcptNo as RcptNo, 0 as MemoNo, 0 as MDate, Receipt_CLNT.G_Name
from Receipt_CLNT
ORDER BY Consignee, MemoNo, LR_No;

Select allOpen in new window

>it says "The Number of columns in the two selected tables or queries of a union query do not match
as the error message says: the 2 selects must have the same number of columns.

you have:

Select 0 as Rcpt_Date
        , 0 as CmNo
        , BD.Bdate as BookDate
       , BD.BiltyNo as LR_No
       , 0 as CM_Total
        , 0 as Amt_Rcvd
          , 0 as ChqDDNo
           , 0 as ChqDDdate
        , 0 as Acc_Typ
         , 0 as RcptNo
       ,BD.CST as MemoNo
        , BD.GRDate as M_Date
 , BD.Consignee 
from  ....
UNION ALL SELECT CM.CM_Date AS Cdate
    , CM.Cash_MemoNo AS CmNo
      ,0 as BookDate
   , 0 as LR_No
   , CM.CM_Total as CMAmt
  , 0 as Amt_Rcvd
   , 0 as ChqDDNo
   , 0 as ChqDDdate
     , 0 as Acc_Typ
    , 0 as RcptNo 
   ,0 as MemoNo
  , 0 as M_Date 
from ...
UNION ALL SELECT Receipt_CLNT.Rcpt_Date
    , 0 as CmNo
    , 0 as BookDate
   , 0 as LR_No
    , 0 as CM_Total
   , Receipt_CLNT.Amt_Rcvd as RcptAmt
    ,Receipt_CLNT.ChqDDNo as RefNo
   , Receipt_CLNT.ChqDDdate as RefDate
  , Receipt_CLNT.Amt_Mode as Mode
   , Receipt_CLNT.RcptNo as RcptNo
   , 0 as MemoNo
  , 0 as MDate
  , Receipt_CLNT.G_Name
from ... 

Select allOpen in new window

you can see like this where you have the issue ?!
your second select seems to have 1 column less than the others ...

oh yeh I forgot to add a field, and i placed first table to second and second to first.
first problem is when i run this query its asking me to put parameter ( CM.CNee ).
and when i click on ok without parameter value, getting records but
Second problem is in these records i am not getting data from BD table
Fields from BD table are
BookDate, LR_No, MemoNo, M_Date

Please check snap also

SELECT CM.CM_Date AS Cdate, CM.Cash_MemoNo AS CmNo,0 as BookDate, 0 as LR_No, CM.CM_Total as CMAmt, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,0 as MemoNo, 0 as M_Date, CM.CNee as conName
from (CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))
UNION ALL 
Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD 
INNER JOIN Receipt_CLNT CM ON (CM.CNee = BD.Consignee))
UNION ALL SELECT Receipt_CLNT.Rcpt_Date, 0 as CmNo, 0 as BookDate, 0 as LR_No, 0 as CM_Total, Receipt_CLNT.Amt_Rcvd as RcptAmt,Receipt_CLNT.ChqDDNo as RefNo, Receipt_CLNT.ChqDDdate as RefDate, Receipt_CLNT.Amt_Mode as Mode, Receipt_CLNT.RcptNo as RcptNo, 0 as MemoNo, 0 as MDate, Receipt_CLNT.G_Name
from Receipt_CLNT
ORDER BY Cdate;

Select allOpen in new window

SQL-Query.doc

>first problem is when i run this query its asking me to put parameter ( CM.CNee ).
that means tha the either CashMemo or Receipt_CLNT does not have the columns.

>Second problem is in these records i am not getting data from BD table
combined with first issue, it's likely Receipt_CLNT table that does not have the CNee column

hence, please review this condition:
from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CM.CNee = BD.Consignee))

yes Receipt_CLNT have (G_Name)  and cash memo have CNee
but both have same value

so you must change:

from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CMG_Name = BD.Consignee))

yes now its not asking me to put parameter value. :-)
But i have two problems now
first is i am not getting data from BD table yet,

and
ORDER BY Cdate; but its not as date sequence

>first is i am not getting data from BD table yet,
you must check your data /  join condition.
you might want to run this to see "better" ...

let's try to solve 1 problem at a time

Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD 
LEFT OUTER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))

Select allOpen in new window

Please check the result.
date is not an order, and
still i am not getting data from BD table
Query-Result-Snap.doc

>still i am not getting data from BD table
with the snap, I cannot tell you anything, as I don't know the input data ...

>date is not an order,
please try to use
ORDER BY 1

>>date is not an order,
my suggestion will only work if the field is datetime data type, actually. please clarify

yes i am getting data from this query

Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD 
LEFT OUTER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))

Select allOpen in new window

CM AND Receipt_CLNT both table have date/time type field

>CM AND Receipt_CLNT both table have date/time type field
well, then I don't understand why the order by does not work

ok i will manage order by
but why i am not getting detail from BD table

without seeing the data from the 2 tables, I cannot help ...

you mean to field name from BD table ...?

BD.Bdate as BookDate,       12-04-2011

BD.BiltyNo as LR_No,          4290345

BD.CST as MemoNo,            12054

BD.GRDate as M_Date,        18-04-2011

BD.Consignee                       Mark and company

UNION ALL 
Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 
0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 
0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee
from (Bilty_Detail BD

Select allOpen in new window

no, I referred to a "full" sample (like the screenshot you had posted) of the 2 tables you join, for just a couple of records that shall "match".

herewith all details attached please check

SELECT CM.CM_Date AS Cdate, CM.Cash_MemoNo AS CmNo,0 as BookDate, 0 as LR_No, CM.CM_Total as CMAmt, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,0 as MemoNo, 0 as M_Date, CM.CNee as conName
from (CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))
UNION ALL 
Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))
UNION ALL SELECT Receipt_CLNT.Rcpt_Date, 0 as CmNo, 0 as BookDate, 0 as LR_No, 0 as CM_Total, Receipt_CLNT.Amt_Rcvd as RcptAmt,Receipt_CLNT.ChqDDNo as RefNo, Receipt_CLNT.ChqDDdate as RefDate, Receipt_CLNT.Amt_Mode as Mode, Receipt_CLNT.RcptNo as RcptNo, 0 as MemoNo, 0 as MDate, Receipt_CLNT.G_Name
from Receipt_CLNT
ORDER BY Cdate;

Select allOpen in new window

Detail-of-Tables.doc

Hi:
i am getting result using suggested query,
but data is not like what i wanted,
Please check attached file
desired and query data snap is in that file

Hope you understand my problem

Thanks in advance

SELECT CM.CM_Date AS Cdate, CM.Cash_MemoNo AS CmNo,0 as BookDate, 0 as LR_No, CM.CM_Total as CMAmt, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,0 as MemoNo, 0 as M_Date, CM.CNee as conName
from (CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))
UNION ALL 
Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))
UNION ALL SELECT Receipt_CLNT.Rcpt_Date, 0 as CmNo, 0 as BookDate, 0 as LR_No, 0 as CM_Total, Receipt_CLNT.Amt_Rcvd as RcptAmt,Receipt_CLNT.ChqDDNo as RefNo, Receipt_CLNT.ChqDDdate as RefDate, Receipt_CLNT.Amt_Mode as Mode, Receipt_CLNT.RcptNo as RcptNo, 0 as MemoNo, 0 as MDate, Receipt_CLNT.G_Name
from Receipt_CLNT
ORDER BY Cdate;

Select allOpen in new window

Query-and-Desire-Result.doc

Thanks angell
Please attend following question as it is related to answerd one.

https://www.experts-exchange.com/questions/26975238/UNION-ALL-WITH-WHERE-CLAUSE-1.html