• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

UNION ALL WITH WHERE CLAUSE


currently i am using following query to join two table
but now i want to add where clause from third table
how can i do this

like following data i am getting from two table using attached query,
But now i want same data but where BD.CONSIGNEE = thirdtable.CONSIGNEE

MemoNo - M_Date - LR_No - Cros_No - Vasu - Crossi - Balanc - Amt_Rcvd - CONSIGNEE

how can i do this
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;

Open in new window

0
crystal_Tech
Asked:
crystal_Tech
  • 18
  • 13
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the basic "syntax" for union is:
select ... from ... where ...
UNION
select ... from ... where ... 

Open in new window

etc.
0
 
crystal_TechAuthor Commented:

YES BUT ITS ASKING ME TO PUT VALUE
0
 
mayank_joshiCommented:
 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

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mayank_joshiCommented:
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;

Open in new window

0
 
mayank_joshiCommented:
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;

Open in new window

0
 
crystal_TechAuthor Commented:
not working
third table name is ClientLedger

database is Access 2003 (Not SQL Server)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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;

Open in new window

0
 
crystal_TechAuthor Commented:

@angelIII:
Yes thats i wanted
Let me try
0
 
crystal_TechAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this ?
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 RcptCLNT CM ON (CM.CONSIGNEE = BD.CONSIGNEE ))
INNER JOIN CLedger ON (CLedger.CNee = CM.Cnee)
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;

Open in new window

0
 
crystal_TechAuthor Commented:
@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 ;-)
 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
 
crystal_TechAuthor Commented:

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;

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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 ... 

Open 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 ...
0
 
crystal_TechAuthor Commented:
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;

Open in new window

SQL-Query.doc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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))
0
 
crystal_TechAuthor Commented:
yes Receipt_CLNT have (G_Name)  and cash memo have CNee
but both have same value
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you must change:

from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CMG_Name = BD.Consignee))
0
 
crystal_TechAuthor Commented:

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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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))

Open in new window

0
 
crystal_TechAuthor Commented:
Please check the result.
date is not an order, and
still i am not getting data from BD table
Query-Result-Snap.doc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>>date is not an order,
my suggestion will only work if the field is datetime data type, actually. please clarify
0
 
crystal_TechAuthor Commented:

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))

Open in new window

0
 
crystal_TechAuthor Commented:

CM AND Receipt_CLNT both table have date/time type field
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>CM AND Receipt_CLNT both table have date/time type field
well, then I don't understand why the order by does not work
0
 
crystal_TechAuthor Commented:
ok i will manage order by
but why i am not getting detail from BD table
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
without seeing the data from the 2 tables, I cannot help ...
0
 
crystal_TechAuthor Commented:
you mean to field name from BD table ...?
0
 
crystal_TechAuthor Commented:
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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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".
0
 
crystal_TechAuthor Commented:
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;

Open in new window

Detail-of-Tables.doc
0
 
crystal_TechAuthor Commented:

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;

Open in new window

Query-and-Desire-Result.doc
0
 
crystal_TechAuthor Commented:
Thanks angell
Please attend following question as it is related to answerd one.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_26975238.html
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 18
  • 13
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now