Velocity Reviews - Computer Hardware Reviews

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

Reply
Thread Tools

LEFT JOIN problem

 
 
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:

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

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?

--
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 07:40:33 -0400:

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


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


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

--
Dan


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

> Daniel Crichton wrote:
>> Bob wrote on Thu, 18 Jun 2009 19:41:12 -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.

--
Dan


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


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


 
Reply With Quote
 
Mary
Guest
Posts: n/a
 
      06-19-2009
Thanks for you reminder. In fact, I am using BMonth and BYear in my clause.

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



 
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