Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > LEFT JOIN problem

Reply
Thread Tools

LEFT JOIN problem

 
 
Mary
Guest
Posts: n/a
 
      06-18-2009
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 !


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      06-18-2009
Mary wrote:
> 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.

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


 
Reply With Quote
 
 
 
 
Mary
Guest
Posts: n/a
 
      06-19-2009
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 !


"Bob Barrows" <(E-Mail Removed)> glsD:(E-Mail Removed) bl...
> Mary wrote:
>> 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.
>
> --
> 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"
>



 
Reply With Quote
 
Daniel Crichton
Guest
Posts: n/a
 
      06-19-2009
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.

--
Dan



 
Reply With Quote
 
Daniel Crichton
Guest
Posts: n/a
 
      06-19-2009
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).

--
Dan


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      06-19-2009
Daniel Crichton wrote:
> 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.


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.

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


 
Reply With Quote
 
Daniel Crichton
Guest
Posts: n/a
 
      06-19-2009
Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:

> Daniel Crichton wrote:
>> 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.


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


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.

--
Dan


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      06-19-2009
Mary wrote:
> 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.

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


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      06-19-2009
Daniel Crichton wrote:
> Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:
>
>> Daniel Crichton wrote:
>>> 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.

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

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



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


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


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      06-19-2009
Daniel Crichton wrote:
> 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).


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.



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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LINQ left outer join John Thomas ASP .Net 1 09-01-2009 05:41 PM
You must join New York Paid To Read !!!!!!! NewYork PTR is online!!! JOIN NOW!!!!!! Alan Silver ASP .Net 0 06-05-2006 03:27 PM
list.join()... re.join()...? Do they exist? (newbie questions...) googleboy Python 1 10-01-2005 12:56 PM
2nd try, left join Jeff Uchtman ASP General 11 04-12-2004 02:17 AM
HELP! Left, Right, Inner Join Jeff Uchtman ASP General 0 04-07-2004 07:14 AM



Advertisments