Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Photoshopgod
Starting Member
10 Posts |
 Posted - 2004-07-25 : 16:51:32
|
| Hi,I have returned to this project and have run into a problem. I'm getting this error message when I run my sql statement;Microsoft JET Database Engine error '80040e14' Join expression not supported.Here is my sql statement;strsql = "SELECT * FROM [tblCustomers], [tblCustomers] left join [tblParents] [tblParents] on tblCustomers.custID = tblParents.pcustID, [tblCustomers] left join [tblCouplesInfo] [tblCouplesInfo] on tblCustomers.custID = tblCouplesInfo.cicustID, [tblCustomers] left join [tblEmailAddresses] [tblEmailAddresses] on tblCustomers.custID = tblEmailAddresses.eacustID WHERE tblCustomers.custID = " & tkeySorry for the length of the statement, I haven't written it with alliases yet. What I have is a main table called tblCustomers. tblParents, tblCouplesInfo, and tblEmailAddresses all have supporting information that I need to display on a web page called view.asp. My original statement only had one left join and worked fine. This is my first attempt at using multiple left joins. I know I'm missing something very obviouse. Thanks in advance for any help. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-25 : 18:18:24
|
| how about formatting it so we can read it-ec |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-25 : 19:29:58
|
| Have you tried running the query directly in Access query designer? Judging from the format of the statement you're coding the query directly into a procedure. Btw - You will get this error if you try and open a query in the Query Designer if it uses non-standard joins (e.g. LEFT JOIN Tbl1 ON Tbl.ID = Cint(Tbl1.T_ID) ). But it doesn't show the error if you try and execute the query directly from 'SQL Mode' in the QD. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-26 : 17:03:36
|
| There are a couple of problems with your SELECT statement. First, you have table names directly preceding your LEFT JOIN statements and following the commas. I've never seen this used before. Second, you don't need commas in between JOIN statements. Third, Access likes to have parentheses inserted so that every join appears to be only between two resultsets (or, they sometimes do some bizarre nesting within their designer that I have never been able to decipher.So, in the end, it would end up looking SOMETHING LIKE the following:SELECT * FROM ((([tblCustomers]LEFT JOIN [tblParents] [tblParents] on tblCustomers.custID = tblParents.pcustID)LEFT JOIN [tblCouplesInfo] [tblCouplesInfo] on tblCustomers.custID = tblCouplesInfo.cicustID)LEFT JOIN [tblEmailAddresses] [tblEmailAddresses] on tblCustomers.custID = tblEmailAddresses.eacustID)WHERE ...If you can't get it working from this example, go into the Query Designer, use the GUI to join the tables, then switch over to SQL View to see what the syntax is.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
Photoshopgod
Starting Member
10 Posts |
Posted - 2004-07-26 : 21:16:30
|
| Thanks AjarnMark,I've learned so much from my posts as well as other posts you've replied to. You have a great way of explaining why something doesn't work and how to fix it. It's great for someone like me who is still learning sql. Thanks again. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-27 : 13:17:04
|
Aw, shucks, glad I could help.That's the teacher in me coming out. 'Course it's also the way I like to get answers so I actually learn from it rather than just get the answer handed to me.Stick around, there's lots of good learning going on here. -----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
|
|
|
|
|
sql statement question