LEFT JOIN problem

[Mary]

I have two databases and want to combine the data like below:

BRecord.mdb.BRecord
ProductID Qty Year Month
A101 20 2009 6
A102 30 2009 6

Goods.mdb.Goods
ProductID Name
A101 Flower
A102 Dog

I want to get the result as below
ProductID Qty Name
A101 20 Flower
A102 30 Dog

I try the following clause but failed,

Goods = Server.MapPath("Goods.mdb")
BRecord = Server.MapPath("BRecord2004.mdb")

SQL = "Select ProductID, SUM(Qty) As EEE From ("
SQL = SQL & "Select ProductID From " & BRecord & ".BRecord Where Year =
2009 And Month = 6"
SQL = SQL & " LEFT JOIN Name On " & BRecord & ".BRecord.ProductID = " &
Goods & ".Goods.ProductID"
SQL = SQL & ") Group By ProductID Order By ProductID"

Can anyone help me ? Thanks !

 

[Bob Barrows]

I have two databases and want to combine the data like below:

Type and version of database please? It is almost always relevant.

BRecord.mdb.BRecord

OK, I think I see that this is Access (Jet) but you should start by telling
us this, instead of making us guess.

ProductID Qty Year Month
A101 20 2009 6
A102 30 2009 6

Goods.mdb.Goods
ProductID Name
A101 Flower
A102 Dog

I want to get the result as below
ProductID Qty Name
A101 20 Flower
A102 30 Dog

This appears to be a simple join to me. Why do you use the word "LEFT" in
your subject?

The only problem I see is that these are different database files. You will
not be able to perform a sql join between them given that they are in
different databases, unless one of the databases has a link to the table in
the other database.

I try the following clause but failed,

Please describe your symptoms without using the words "didn't work", or
"failed", or "no joy". Did you receive an error message? Incorrect result?
Did your computer crash? Did the world end?

Goods = Server.MapPath("Goods.mdb")
BRecord = Server.MapPath("BRecord2004.mdb")

SQL = "Select ProductID, SUM(Qty) As EEE From ("
SQL = SQL & "Select ProductID From " & BRecord & ".BRecord Where
Year = 2009 And Month = 6"
SQL = SQL & " LEFT JOIN Name On " & BRecord & ".BRecord.ProductID =
" & Goods & ".Goods.ProductID"
SQL = SQL & ") Group By ProductID Order By ProductID"

Well, this seems very wrong-headed (sorry). A Join statement requires two
database tables on either side:
table1 join table2
You have a table and what appears to be a field. Please show us the result
of :
Response.Write SQL

If you have created a sql statement correctly, you should be able to open
your database file in Access, create a new query, switch it to sql view,
paste in the sql statement from the browser window and run it without
modification.

 

[Mary]

Dear Bob,

The version of two databases is "Microsoft.Jet.OLEDB.4.0"

Yes, I am newbie in using "Join", especially join on two different database.
Since I search through the internet, most users join two tables within one
database, it seems very simple, unfortunately I can't find a sample that
join two tables in two different databases, thus I copy some clauses to
modify but can't make the way, this my problems.

I am using frontpage 2003 to write the program, and back to IE to see the
result . Each time if the SQL statement failed, the browser will only tell
me "Here needs an object", no hints what going wrong. That make me crazy .

Fine, by using "Response.write SQL" , I get the output as below :

Select ProductID, SUM(Qty) As EEE From (Select ProductID From
D:\Sites\Pos\BRecord.mdb.BRecord where Year = 2009 And Month = 6 And BID > 0
LEFT JOIN Name On D:\Sites\Pos\BRecord2004.mdb.BRecord.ProductID =
D:\Sites\Pos\Goods.mdb.Goods.ProductID) Group By ProductID Order By
ProductID

The only thing I need is to catch the name from Goods.mdb to the left side
of ProductID in Record.mdb, but the difficult is they are two different
databases.

Thansk for yr response very much !

 

[Daniel Crichton]

Mary wrote on Fri, 19 Jun 2009 05:37:48 +0800:

I have two databases and want to combine the data like below:

BRecord.mdb.BRecord
ProductID Qty Year Month
A101 20 2009 6
A102 30 2009 6

Goods.mdb.Goods
ProductID Name
A101 Flower
A102 Dog

I want to get the result as below
ProductID Qty Name
A101 20 Flower
A102 30 Dog

I try the following clause but failed,

Goods = Server.MapPath("Goods.mdb")
BRecord = Server.MapPath("BRecord2004.mdb")

SQL = "Select ProductID, SUM(Qty) As EEE From ("
SQL = SQL & "Select ProductID From " & BRecord & ".BRecord Where Year
= 2009 And Month = 6"
SQL = SQL & " LEFT JOIN Name On " & BRecord & ".BRecord.ProductID = "
& Goods & ".Goods.ProductID"
SQL = SQL & ") Group By ProductID Order By ProductID"

Can anyone help me ? Thanks !

Your SQL is invalid - you're using Name in the JOIN but then joining using
Goods, and your JOIN should be before the WHERE. You also don't need the
subquery. And if every ProductID has a matching row in Goods then you don't
use a LEFT join, you use an INNER. I think this will work, but I haven't
tested it.

Select b.ProductID, SUM(b.Qty) as Qty, g.Name
From " & BRecord & ".BRecord as b
INNER JOIN " & Goods & ".Goods as g On b.ProductID = g.ProductID
Where b.Year = 2009 And b.Month = 6
Group By b.ProductID, g.Name
Order By b.ProductID

It's a lot simpler to use a table alias than keep using the full mdb path
which is why the "as b" and "as g" are in there. If you do need a LEFT JOIN
because there are BRecord rows that don't have a matching Goods row then
just change INNER JOIN to LEFT JOIN, but be aware that the Name column in
the results will be NULL where there is no matching Goods row.

 

[Daniel Crichton]

Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:

The only problem I see is that these are different database files. You
will not be able to perform a sql join between them given that they
are in different databases, unless one of the databases has a link to
the table in the other database.

Bob, something I realised when testing my response to an earlier post Mary
made is that it is possible

Jet allows the use of the full path to the mdb file along with the table
name in that file as the table identifier in queries. For instance,

SELECT * FROM c:\database\mydata.mdb.Products

will return all rows from the Products table in the c:\database\mydata.mdb
file.

I never realised this was possible myself until recently. It certainly makes
it simpler than using linked tables, especially in a hosted environment
where the path to the external mdb file may change (site moved to another
drive/server without notice).

 

[Bob Barrows]

Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:


Bob, something I realised when testing my response to an earlier post
Mary made is that it is possible

Jet allows the use of the full path to the mdb file along with the
table name in that file as the table identifier in queries. For
instance,
SELECT * FROM c:\database\mydata.mdb.Products

will return all rows from the Products table in the
c:\database\mydata.mdb file.

Really? I knew about using the IN operator to get to an external database
but I have never seen this syntax.

I never realised this was possible myself until recently. It
certainly makes it simpler than using linked tables, especially in a
hosted environment where the path to the external mdb file may change
(site moved to another drive/server without notice).

Have you gotten it to work, Daniel? Maybe I'll take a few minutes and give
it a try myself this morning.

Mary, if this syntax does indeed work via ADO, and assuming your connection
string is pointing at Goods.mdb, your statement should become:

Select ProductID, SUM(Qty) As EEE From
D:\Sites\Pos\BRecord.mdb.BRecord as r
LEFT JOIN Goods as g On r.ProductID =
g.ProductID
where Year = 2009 And Month = 6 And BID > 0
Group By ProductID
Order By ProductID

And you really, really should accustom yourself to testing your queries _in
Access_ before attempting to create them in an external application. If the
query does not work in Access, there is no hope that it will work when
executed from ASP.

 

[Daniel Crichton]

Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:

Really? I knew about using the IN operator to get to an external
database but I have never seen this syntax.

As I said, until Mary's first post title "Select INTO, UNION" I'd never seen
it before either, but I ran some tests with some Access databases here and
was amazed to find it worked. I was going to reply to that saying that IN
should be used but this syntax is actually somewhat easier to read. I'm
guessing that the mdb full path will need to be enclosed in [] if it
contains a space, but I didn't test that aspect.

Have you gotten it to work, Daniel? Maybe I'll take a few minutes and
give it a try myself this morning.

Yes, quite a few times. I even tested it on a live ASP site that uses an
Access database (it's waiting to be migrated to PHP and MySQL, and doesn't
have access to SQL Server).

Mary, if this syntax does indeed work via ADO, and assuming your
connection string is pointing at Goods.mdb, your statement should
become:

Select ProductID, SUM(Qty) As EEE From
D:\Sites\Pos\BRecord.mdb.BRecord as r
LEFT JOIN Goods as g On r.ProductID =
g.ProductID where Year = 2009 And Month = 6 And BID > 0
Group By ProductID
Order By ProductID

As ProductID occurs in both r and g, will this work? I can't remember if
Access requires a column that appears in mutiple tables to be qualified with
the table name/alias, but SQL Server certainly does (which is where I do
most of my development).

And you really, really should accustom yourself to testing your queries
_in Access_ before attempting to create them in an external application.
If
the query does not work in Access, there is no hope that it will work
when executed from ASP.

I totally agree with this suggestion. It's by far the easiest way to
prototype and debug SQL for Access.

 

[Bob Barrows]

Dear Bob,

The version of two databases is "Microsoft.Jet.OLEDB.4.0"

No, that is the name of the provider being used to connect to one of them.
What version of Access was used to create them?

Yes, I am newbie in using "Join", especially join on two different
database. Since I search through the internet, most users join two tables
within one database, it seems very simple, unfortunately I can't find a
sample
that join two tables in two different databases, thus I copy some clauses
to modify but can't make the way, this my problems.

I am using frontpage 2003 to write the program, and back to IE to see
the result . Each time if the SQL statement failed, the browser will only
tell me "Here needs an object", no hints what going wrong. That make me
crazy .

That's because you are not creating and testing your sql statement _in
Access_ using the query builder. This is extremely important to your future
sanity

Fine, by using "Response.write SQL" , I get the output as below :

Select ProductID, SUM(Qty) As EEE From (Select ProductID From
D:\Sites\Pos\BRecord.mdb.BRecord where Year = 2009 And Month = 6 And
BID > 0 LEFT JOIN Name On D:\Sites\Pos\BRecord2004.mdb.BRecord.ProductID =
D:\Sites\Pos\Goods.mdb.Goods.ProductID) Group By ProductID Order By
ProductID

The only thing I need is to catch the name from Goods.mdb to the left
side of ProductID in Record.mdb, but the difficult is they are two
different databases.

See my other responses in reply to Daniel's message.

 

[Bob Barrows]

Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:

Really? I knew about using the IN operator to get to an external
database but I have never seen this syntax.

As I said, until Mary's first post title "Select INTO, UNION" I'd
never seen it before either, but I ran some tests with some Access
databases here and was amazed to find it worked. I was going to reply
to that saying that IN should be used but this syntax is actually
somewhat easier to read. I'm guessing that the mdb full path will
need to be enclosed in [] if it contains a space, but I didn't test
that aspect.

I'm having trouble getting it to work.

Yes, quite a few times. I even tested it on a live ASP site that uses
an Access database (it's waiting to be migrated to PHP and MySQL, and
doesn't have access to SQL Server).



As ProductID occurs in both r and g, will this work?

Duh! Of course not! the ProductID field has to be explictly qualified using
the alias.

Select r.ProductID, SUM(Qty) As EEE From
D:\Sites\Pos\BRecord.mdb.BRecord as r
LEFT JOIN Goods as g On r.ProductID =
g.ProductID
where Year = 2009 And Month = 6 And BID > 0
Group By r.ProductID
Order By r.ProductID

Again, the importance of testing in the database environment becomes
evident.

And Mary, I'm still puzzled why you think you need a LEFT join. Using your
sample data, an INNER join will produce the same results:
Select r.ProductID, SUM(Qty) As EEE From
D:\Sites\Pos\BRecord.mdb.BRecord as r
JOIN Goods as g On r.ProductID =
g.ProductID
where Year = 2009 And Month = 6 And BID > 0
Group By r.ProductID
Order By r.ProductID

 

[Bob Barrows]

Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:


Bob, something I realised when testing my response to an earlier post
Mary made is that it is possible

Jet allows the use of the full path to the mdb file along with the
table name in that file as the table identifier in queries. For
instance,
SELECT * FROM c:\database\mydata.mdb.Products

will return all rows from the Products table in the
c:\database\mydata.mdb file.

I never realised this was possible myself until recently. It
certainly makes it simpler than using linked tables, especially in a
hosted environment where the path to the external mdb file may change
(site moved to another drive/server without notice).

Your suggested syntax did not work. I had to resort to using the IN clause,
like this:

SELECT * from stations in "C:\Docume~1\Bob\My Documents\mdb_files\db1.mdb"

Testing this in a vbscript file using this code was successful:

dim cn,rs
set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=C:\Docume~1\Bob\MyDocu~1\mdb_files\db3.mdb"
sql="SELECT * from stations in " & _
"""C:\Docume~1\Bob\MyDocu~1\mdb_files\db1.mdb"""
set rs=cn.execute( ,1)
msgbox rs.getstring

So there is a good possibility that this will work in ASP given that
permissions are set correctly.
Note: Mary, the quotation marks must be "escaped" by doubling them when
using them as literals in te string you are building. This is one of the
reasons I strongly recommend using saved queries in your database instead of
dynamic sql.

From Access online help:
Notes
For improved performance and ease of use, use a linked table instead of IN.

 

[Bob Barrows]

Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:



Yes, quite a few times. I even tested it on a live ASP site that uses
an Access database (it's waiting to be migrated to PHP and MySQL, and
doesn't have access to SQL Server).

OK, I found the secret:
SELECT *
FROM [C:\Docume~1\Bob\MyDocu~1\mdb_files\db1.mdb].stations

The path to the database _file_ needs to be enclosed in brackets. Not the
entire table expression.

Hmm, online help gives no clue that this is possible ...

Mary, assuming you are bent on using dynamic sql, and assuming you don't
really need a LEFT join, your code should become:

SQL="Select r.ProductID, SUM(Qty) As EEE,g.[Name] From " & _
"[D:\Sites\Pos\BRecord.mdb].BRecord as r " & _
"JOIN Goods as g On r.ProductID =g.ProductID " & _
"where Year = 2009 And Month = 6 And BID > 0 " & _
"Group By r.ProductID,g.[Name] " & _
"Order By r.ProductID"

If your sample data is incomplete, and you really do need a left join, just
modify the above accordingly.

Wait ... your sample data contains only one record per ProductID - why are
you grouping and using SUM? Is your sample data incomplete?

 

[Daniel Crichton]

Bob wrote on Fri, 19 Jun 2009 07:40:33 -0400:

OK, I found the secret:
SELECT *
FROM [C:\Docume~1\Bob\MyDocu~1\mdb_files\db1.mdb].stations

The path to the database _file_ needs to be enclosed in brackets. Not
the entire table expression.

So far all my tests have been without spaces, so I've just left the [] out,
eg.

select * from e:\mo\data\mo.mdb.products

which gives me all the rows from the products table in my external database

Hmm, online help gives no clue that this is possible ...

It was only by trying Mary's SQL that I found out it was possible. I wonder
where she found it ...

 

[Daniel Crichton]

Bob wrote on Fri, 19 Jun 2009 07:30:10 -0400:

From Access online help:
Notes
For improved performance and ease of use, use a linked table instead of
IN.

I'm guessing that's because Access stores the table definition in the local
mdb file for a linked table and so will assume it's correct and won't have
to determine the structure first. Of course, if like me during prototyping
the structure changes often linked tables can be a pain if you forget to
relink ...

For most applications I'd assume that the performance impact will be pretty
small - if it becomes a problem then it'll likely be worth moving to SQL
Server Express instead anyway.

 

[Mary]

I think my life has got a great leap today, thanks for Daniel and Bob .

I totally sucess using "JOIN" and "UNION" across two different databases,
now I get the result very easy and also, very fast.

I would like to give publicity to these statements, to those who hope to
understand how to make a clause across two different database.

JOIN
Goods = Server.MapPath("Goods.mdb")
BRecord = Server.MapPath("BRecord.mdb")
SQL = "Select b.ProductID, SUM(b.Qty) as Qty, g.Name From " & BRecord &
".BRecord as b"
SQL = SQL & " INNER JOIN " & Goods & ".Goods as g On b.ProductID =
g.ProductID"
SQL = SQL & " Where b.Year = 2009 And b.Month = 6"
SQL = SQL & " Group By b.ProductID, g.Name"
SQL = SQL & " Order By b.ProductID"
Set rs = GetMdbRecordset( "BRecord.mdb" , SQL)

By using "Response.write SQL" return the following statement :
Select b.ProductID, SUM(b.Qty) as Qty, g.Name From
D:\Sites\Pos\BRecord.mdb.BRecord as b INNER JOIN
D:\Sites\Pos\Goods.mdb.Goods as g On b.ProductID = g.ProductID Where b.Year
= 2009 And b.Month = 6 Group By b.ProductID, g.Name Order By b.ProductID

I have two databases and want to combine the data like below:

BRecord.mdb.BRecord
ProductID Qty Year Month
A101 20 2009 6
A102 30 2009 6

Goods.mdb.Goods
ProductID Name
A101 Flower
A102 Dog

I want to get the result as below
ProductID Qty Name
A101 20 Flower
A102 30 Dog

----------------------------------------------------------
UNION

DMonth = 2009
DYear = 6

SAC = Server.MapPath("MDSAC.mdb")
Hope = Server.MapPath("MDHope.mdb")
Unhcr = Server.MapPath("MDUnhcr.mdb")

DIM SQL, rs
Set objconn = GetMdbConnection( "Total.mdb") ' This mdb is no use, no table
inside, however must exist
SQL = "Select FNo, FName, SUM(DAmount) As EEE From ("
SQL = SQL & "Select FNo, FName, DAmount From " & SAC & ".MData Where DYear
= " & DYear & " And DMonth = " & DMonth & " And IsNull(Void) = True"
SQL = SQL & " UNION ALL "
SQL = SQL & "Select FNo, FName, DAmount From " & Hope & ".MData Where DYear
= " & DYear & " And DMonth = " & DMonth & " And IsNull(Void) = True"
SQL = SQL & " UNION ALL "
SQL = SQL & "Select FNo, FName, DAmount From " & Unhcr & ".MData Where DYear
= " & DYear & " And DMonth = " & DMonth & " And IsNull(Void) = True"
SQL = SQL & ") Group By FNo, FName Order By SUM(DAmount) desc"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL, objConn
While Not rs.EOF
.....rs("FNo") & "-" & rs("FName") & "-" & rs("EEE")
rs.MoveNext
Wend

By using "Response.write SQL" return the following statement :

Select FNo, FName, SUM(DAmount) As EEE From (Select FNo, FName, DAmount From
D:\Sites\fund\MDSAC.mdb.MData Where DYear = 2009 And DMonth = 6 And
IsNull(Void) = True UNION ALL Select FNo, FName, DAmount From
D:\Sites\fund\MDHope.mdb.MData Where DYear = 2009 And DMonth = 6 And
IsNull(Void) = True UNION ALL Select FNo, FName, DAmount From
D:\Sites\fund\MDUnhcr.mdb.MData Where DYear = 2009 And DMonth = 6 And
IsNull(Void) = True) Group By FNo, FName Order By SUM(DAmount) desc

Hope this help to other people !!

 

[Bob Barrows]

Goods = Server.MapPath("Goods.mdb")
BRecord = Server.MapPath("BRecord.mdb")
SQL = "Select b.ProductID, SUM(b.Qty) as Qty, g.Name From " & BRecord
& ".BRecord as b"
SQL = SQL & " INNER JOIN " & Goods & ".Goods as g On b.ProductID =
g.ProductID"
SQL = SQL & " Where b.Year = 2009 And b.Month = 6"
SQL = SQL & " Group By b.ProductID, g.Name"
SQL = SQL & " Order By b.ProductID"
Set rs = GetMdbRecordset( "BRecord.mdb" , SQL)

I totally neglected to mention the reserved keywords you (or the database
developer) are using for your fieldnames. "Name", "Year" and "Month" are all
reserved keywords ("Name" is a very common property name in VBA, and Year()
and Month() are the names of VBA functions) and, even if they are not
causing you grief at the moment. it is almost guaranteed that someday you
will run into a very hard-to-debug problem caused by their use. You can
mitigate the danger by always remembering to surround them with brackets ("
.... g.[Name] ... b.[Year] ... b.[Month] ...) in your sql statements, but the
best course by far is to give them more meaningful names to avoid using
reserved keywords. For example, what does that Name field contain? The name
of the product? Wouldn't it make more sense to call it "ProductName"? Two
problems solved in a single stroke: no more reserved keyword, and no more
need to explain to someone what the field contains. I would guess that
YearOfSale and MonthOfSale would be the appropriate names of the other
fields ... or not ... maybe they contain the year and month that the record
was entered ...

Here is a list of reserved keywords to avoid:

http://www.aspfaq.com/show.asp?id=2080

 

[Mary]

Thanks for you reminder. In fact, I am using BMonth and BYear in my clause.

Goods = Server.MapPath("Goods.mdb")
BRecord = Server.MapPath("BRecord.mdb")
SQL = "Select b.ProductID, SUM(b.Qty) as Qty, g.Name From " & BRecord
& ".BRecord as b"
SQL = SQL & " INNER JOIN " & Goods & ".Goods as g On b.ProductID =
g.ProductID"
SQL = SQL & " Where b.Year = 2009 And b.Month = 6"
SQL = SQL & " Group By b.ProductID, g.Name"
SQL = SQL & " Order By b.ProductID"
Set rs = GetMdbRecordset( "BRecord.mdb" , SQL)

I totally neglected to mention the reserved keywords you (or the database
developer) are using for your fieldnames. "Name", "Year" and "Month" are
all reserved keywords ("Name" is a very common property name in VBA, and
Year() and Month() are the names of VBA functions) and, even if they are
not causing you grief at the moment. it is almost guaranteed that someday
you will run into a very hard-to-debug problem caused by their use. You
can mitigate the danger by always remembering to surround them with
brackets (" ... g.[Name] ... b.[Year] ... b.[Month] ...) in your sql
statements, but the best course by far is to give them more meaningful
names to avoid using reserved keywords. For example, what does that Name
field contain? The name of the product? Wouldn't it make more sense to
call it "ProductName"? Two problems solved in a single stroke: no more
reserved keyword, and no more need to explain to someone what the field
contains. I would guess that YearOfSale and MonthOfSale would be the
appropriate names of the other fields ... or not ... maybe they contain
the year and month that the record was entered ...

Here is a list of reserved keywords to avoid:

http://www.aspfaq.com/show.asp?id=2080

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"