Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Joint Two Table Using Query

Posted on 2011-03-19
10
Medium Priority
?
658 Views
Last Modified: 2012-05-11
How to get data from two table which are totally different fields

i am using following code but its not giving me desire result,

    Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Receipt_Tport.G_Name, Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd from Bilty_Detail, Receipt_Tport Where Bilty_Detail.G_Name = Receipt_Tport.G_Name and Receipt_Tport.G_Name = '" & Text1.text & "'", Con, adOpenKeyset, adLockOptimistic

Result is

CST  GRDate    Total       BiltyNo    G_Name            RcptNo  Rcpt_Date  Amt_Rcvd
1       09-03-11  275.00   223334   Wester Trans      1         10-03-11     200.00
2       15-03-11  325.00   223335   Wester Trans      2         17-03-11     100.00
3       16-03-11  125.00   223336   Wester Trans      1         10-03-11     200.00
4       20-03-11  750.00   223337   Wester Trans      2         17-03-11     100.00


Result I want like this as per date and G_Name

CST  GRDate    Total       BiltyNo    G_Name            RcptNo        Rcpt_Date       Amt_Rcvd

1       09-03-11  275.00   223334   Wester Trans      
                                                                                                    1         10-03-11     200.00
2       15-03-11  325.00   223335   Wester Trans      
3       16-03-11  125.00   223336   Wester Trans      
                                                                                                    2         17-03-11     100.00
4       20-03-11  750.00   223337   Wester Trans      


How can I do this
0
Comment
Question by:crystal_Tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35173124
try

Select
        D.CST,
        D.GRDate,
        D.Total,
        D.BiltyNo,
        R.G_Name,
        R.RcptNo,
        R.Rcpt_Date,
        R.Amt_Rcvd
From Bilty_Detail D Left Join Receipt_Tport R On D.G_Name = R.RcptNo
Where R.G_Name = '" & Text1.text & "'",
Con, adOpenKeyset, adLockOptimistic
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35173161
From Bilty_Detail D Left Join Receipt_Tport R On D.G_Name = R.RcptNo

forces the all records show from D table and from R table show the matching ones. Please note that you had it

 D.G_Name = R.G_Name  which I changed it to D.G_Name = R.RcptNo because R table doesn't have field named G_Name.

Also, try firts without Where clause as shown below to make sure the joint woorks okay. We can add the Where portion later.

Rsldgr.Open "Select D.CST, D.GRDate, D.Total, D.BiltyNo, R.G_Name, R.RcptNo, R.Rcpt_Date, R.Amt_Rcvd From Bilty_Detail D Left Join Receipt_Tport R On D.G_Name = R.RcptNo", Con, adOpenKeyset, adLockOptimistic
 
Mike
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35173171
When you join tables there needs to be a record relationship between the records of the tables.

In this case there is but it is based on the "owner" of the transaction.
There is no real relationship between the tables that relates records to each other.

This appears to be some kind of Purchase/charge in the first table and a payment in the second.

Tables with relationship example would be a Customer table with CustomerId, Name, Address, Phone etc and an Order table with customer orders that has CustomerId, Order Number, Product List, Amount, Payment type etc.

You query does a JOIN on the Name of the owner thus all records for that owner are linked or joined to every record in the second table.

What you really need is a union of the records with a field added so you can tell which record is from each table.  Something like this

Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Bilty_Detail.G_Name, 'B' as RecType  from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "'" &
"UNION ALL"
"SELECT Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd, 0 as ReceiptNo,  Receipt_Tport.G_Name from Receipt_Tport, 'R' as RecType Where Receipt_Tport.G_Name = '" & Text1.text & "'", Con, adOpenKeyset, adLockOptimistic

mlmcc
0
How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 35175059
>> eghtebas: D.G_Name = R.RcptNo
G_Name is character and RcptNo is Number they cant match...

-----------------------------------------------------------------------------------------

 >>>>> mlmcc:
Suggested query i tried but its displayed an Run Time Error " Syntex error in query, Incomplete query clause.

Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Bilty_Detail.G_Name, 'B' as RecType  from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "' UNION ALL SELECT Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd, 0 as ReceiptNo,  Receipt_Tport.G_Name from Receipt_Tport, 'R' as RecType Where Receipt_Tport.G_Name = '" & Text1.text & "'", con, adOpenKeyset, adLockOptimistic
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35175466
Sorry.  Part of the select is after the where

Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Bilty_Detail.G_Name, 'B' as RecType  from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "' UNION ALL SELECT Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd, 0 as ReceiptNo,  Receipt_Tport.G_Name, 'R' as RecType  from Receipt_Tport  Where Receipt_Tport.G_Name = '" & Text1.text & "'", con, adOpenKeyset, adLockOptimistic

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35179800

sir
Yes i am getting result but i want to change something in query,

Getting result like this
CST   GRDate          Total       BiltyNo    G_Name        Rec_Type
2        01-01-2011   275.00    295743   West Plant     B
1        01-02-2011   100.00    0             West Plant     R
4        09-03-2011   275.00    294367   West Plant     B
2        18-03-2011   200.00    0             West Plant     R


Desire Result like this             (New)                 (New)    (New (Total - pf))  (New)  

CST   GRDate          BiltyNo    GRNo    Total       pf           Balance                Amt_Rcvd      G_Name         Rec_Type
2        01-01-2011   295743   7427      275.00   75.00    200.00                    0                     West Plant     B
0        01-02-2011   0             1            0            0             0                           100.00            West Plant     R
4        09-03-2011   294367   7637      275.00   100.00    175.00                  0                     West Plant     B
0        18-03-2011   0             2            0            0             0                           200.00            West Plant     R

Balance Columns is not in the field but its ( Total - pf = Balance)
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35181090
Is pf a field in the database?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35181171
yes

all this fields in the Bilty_Detail table
CST  
GRDate        
BiltyNo    
GRNo    
Total      
pf
G_Name


and Second table Receipt_Tport has following fields
Rcpt_Date
RcptNo
Amt_Rcvd
G_Name
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 35181331
You can add pf to the Bilty part of the query and add 0 as pf to the second part.

Just make sure they are in the same position.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35181996


Thanks its working now

Please Solved my second question
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26893750.html


0

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question