Multiple left joins in access. Works in MSSQL.
|
View:
New views
4 Messages
—
Rating Filter:
Alert me
|
 |
Multiple left joins in access. Works in MSSQL.
by Michael Grant-12
::
Rate this Message:
I have a query that works fine in mssql and have to port it to access. Trust me I don't want to use access it's not an option for the client to switch to a real db. So I'm stuck trying to port this.
When I run it on the access db I get the following error: "Syntax error (missing operator) in query expression..." Any help would be grand. Thanks in advance. Here's my query. SELECT lc.vendor_new_ID, lc.contact_email, lc.contact_lname, lc.contact_fname, b.new_bid_ID, b.ReferenceNumber, b.Title, b.Description, jb.cccode_ID, bid_ID, jv.cccategory_ID, jv.vendor_ID FROM junction_bid_ccc jb LEFT JOIN junction_vendor_ccc jv ON jb.cccode_ID = jv.cccategory_ID LEFT JOIN lookup_contact lc ON lc.vendor_new_ID = jv.vendor_ID LEFT JOIN Bid b ON b.new_bid_ID = jb.bid_ID WHERE jb.bid_ID = 446 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3157 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6 |
|
|
Re: Multiple left joins in access. Works in MSSQL.
by Michael Grant-12
::
Rate this Message:
It looks like I was a little hasty in posting to the forum. After some messing around I got it to work. For some reason it seems access needs parentheses. Here's the query that works.
SELECT lc.vendor_new_ID, lc.contact_email, lc.contact_lname, lc.contact_fname, b.new_bid_ID, b.ReferenceNumber, b.Title, b.Description, jb.cccode_ID, bid_ID, jv.cccategory_ID, jv.vendor_ID FROM ((junction_bid_ccc jb LEFT JOIN junction_vendor_ccc jv ON jb.cccode_ID = jv.cccategory_ID) LEFT JOIN lookup_contact lc ON lc.vendor_new_ID = jv.vendor_ID) LEFT JOIN Bid b ON b.new_bid_ID = jb.bid_ID WHERE jb.bid_ID = 446 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3158 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6 |
|
|
Re: Multiple left joins in access. Works in MSSQL.
by Joe Graves
::
Rate this Message:
I think this is the syntax.
***Note I have not tested this yet SELECT lc.vendor_new_ID ,lc.contact_email ,lc.contact_lname ,lc.contact_fname ,b.new_bid_ID ,b.ReferenceNumber, b.Title ,b.Description ,jb.cccode_ID ,bid_ID, jv.cccategory_ID ,jv.vendor_ID FROM junction_bid_ccc jb LEFT JOIN ( LEFT JOIN ( LEFT JOIN Bid b ON b.new_bid_ID = jb.bid_ID lookup_contact lc ) ON lc.vendor_new_ID = jv.vendor_ID ) junction_vendor_ccc jv ON jb.cccode_ID = jv.cccategory_ID WHERE jb.bid_ID = 446 Michael Grant wrote: > I have a query that works fine in mssql and have to port it to access. Trust me I don't want to use access it's not an option for the client to switch to a real db. So I'm stuck trying to port this. > > When I run it on the access db I get the following error: > "Syntax error (missing operator) in query expression..." > > Any help would be grand. Thanks in advance. > Here's my query. > > SELECT lc.vendor_new_ID, lc.contact_email, lc.contact_lname, > lc.contact_fname, b.new_bid_ID, b.ReferenceNumber, b.Title, > b.Description, jb.cccode_ID, bid_ID, jv.cccategory_ID, jv.vendor_ID > > FROM junction_bid_ccc jb > > LEFT > JOIN junction_vendor_ccc jv > ON jb.cccode_ID = jv.cccategory_ID > > LEFT > JOIN lookup_contact lc > ON lc.vendor_new_ID = jv.vendor_ID > > LEFT > JOIN Bid b > ON b.new_bid_ID = jb.bid_ID > > WHERE jb.bid_ID = 446 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3159 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6 |
|
|
Re: Multiple left joins in access. Works in MSSQL.
by Michael Grant-12
::
Rate this Message:
Thanks for the reply. I tested it and got the following error.
Syntax error in FROM clause ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3160 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6 |
| Free embeddable forum powered by Nabble | Forum Help |
