Click here to Skip to main content
15,793,452 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to join three table that is in different database and show the result in datagrid view.

First table contain
ID, Group_Id, Voucher, Bill, Vendor_Id, Amount

Second Table Contain
Group_Id, Group_Name

Third Table Contain
Vendor_Id, Vendor_Name, Vendor_Address

I want result like
ID, Group_Name, Voucher, Bill, Vendor_Name, Vendor_Address, Amount

And after joining I want to show these data in Datagridview in C#

My problem is, I am only able to find Inner Join with same database over the internet. But I cant find Inner Join of Tables with different database on same server.

What I have tried:

str1 = "select `ID`, `Group_ID`, `Voucher_No`, `Bill_No`, `Date`, `Vendor_Id`, `Amount` from BillTable where (Group_ID= ''+ @searchsys + '')";
                cmd1 = new OleDbCommand(str1, cn);

                cmd1.Parameters.Add(new OleDbParameter("@searchsys", Convert.ToString(groupidDBS)));

                da1 = new OleDbDataAdapter(cmd1);

                OleDbDataReader reader4 = cmd1.ExecuteReader();
Updated 17-Jul-20 2:47am

You have tagged this as Access but refer to
different database on same server.
therefore I am going to assume it is some kind of SQL.

You can refer to a table by connecting to a specific database and just using its name e.g.
select [ID] from Table1
but you can also give it its "full name" - including the database name and the schema name e.g.
select [ID] from [database2].[dbo].Table1
If you are using Access and the table is in a different .accdb file then use Linked Tables (intro here[^] - but don't call the service!)

EDIT - also - avoid SQL Injection attacks (see SQL Injection | OWASP[^] ) by using Parameterised Queries (see OleDbCommand.Parameters Property (System.Data.OleDb) | Microsoft Docs[^] )
Share this answer
Maciej Los 17-Jul-20 7:41am    
The Digigraphy 17-Jul-20 7:42am    
Hello @CHill60. Sorry to create confusion. It is Access database/
CHill60 17-Jul-20 12:34pm    
In that case I would use Linked tables personally - that way they will "look" as if they are in the same file

The Digigraphy wrote:
My Database Location of First Table is- C:\Users\Digi\Source\Soft\BillData.accdb
And Second Database Location is

Both these database are in different location. And I want to join them

You've got two ways to achieve that:
1) create one database and
a) link all tables from all databases
b) move (import) all tables from different databases into one
See: Import or link to data in another Access database - Access[^]
Then use this query:
SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1 
    INNER JOIN SecondTable t2 ON t1.Group_Id = t2.Group_Id
    INNER JOIN ThirdTable t3 ON t1.Vendor_Id = t3.Vendor_Id
--WHERE t1.Group_Id = ?

I'd reocmmend to use this way.

2) use IN clause[^]:
You'll need to modify the body of query this way:
SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1 --this database is initial database (defined in connection string)
    INNER JOIN SecondTable IN 'C:\Users\Digi\Source\Soft\Master\GroupList.accdb' t2 ON t1.Group_Id = t2.Group_Id
    INNER JOIN ThirdTable IN 'C:\Users\Digi\Source\Soft\ThirdDatabase.accdb' t3 ON t1.Vendor_Id = t3.Vendor_Id
WHERE t1.Group_Id = ?

Above should also work, but it's more complicated.

So, changed code may looks like:
Dim firstFileName As String = "C:\Users\Digi\Source\Soft\BillData.accdb"
Dim secondFileName As String = "C:\Users\Digi\Source\Soft\Master\GroupList.accdb"
Dim thirdFileName As String = "C:\Users\Digi\Source\Soft\Master\ThirdDatabase.accdb"
Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};;Persist Security Info=False;", firstFileName)
Dim dt As DataTable = New DataTable()

Using connection AS OleDbConnection = New OleDbConnection(sConStr)
    Dim sql As String = String.Format("SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount{0}FROM FirstTable t1{0}    INNER JOIN SecondTable IN '{1}' t2 ON t1.Group_Id = t2.Group_Id{0}    INNER JOIN ThirdTable IN '{2}' t3 ON t1.Vendor_Id = t3.Vendor_Id{0}WHERE t1.Group_Id = ?", vbCrLf, secondFileName, thirdFileName)
    Using command As OleDbCommand= New OleDbCommand(sql, connection)
        command.Parameters.Add(New OleDbParameter() With {.OleDbType = OleDbType.VarChar, .Value=2})
        Dim reader As OleDbDataReader = command.ExecuteReader()
    End Using
End Using
'load data into dgv component
DataGridView1.DataSource = dt

Access connection strings -[^]
Share this answer
The Digigraphy 17-Jul-20 7:58am    
Hello Maciej Los, In this query how do I give the table connection?
Like Location of First Table's Database is different from Second Table's Database
Maciej Los 17-Jul-20 8:07am    
Just replace "FirstTable", "SecondTable" and "ThirdTable" with the real names of tables.
The Digigraphy 17-Jul-20 8:48am    
Hey, actually I'm trying to implement it in c#. I wrote following code as you suggest. But this required OleDbConnection for each Table. How do I implement this?

str = "Select t1.ID, t2.Group_Name, t1.Voucher_No, t1.Bill_No, t1.Date, t3.Vendor_Name FROM BillTable t1 INNERJOIN GroupName t2 t1.Group_ID = t2.Group_ID INNERJOIN VendorList t3 ON t1.Vendor_Id = t3.Vendor_Id ";
da = new OleDbDataAdapter(str, cn);
Maciej Los 17-Jul-20 8:57am    
There must be only one connection! You should instantiate it before you create DataAdapter.
The Digigraphy 17-Jul-20 9:03am    
How do I use one connection for different database?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900