Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join Type not supported- Inner Join

Status
Not open for further replies.

jj1576

MIS
May 21, 2008
69
US
Hi,

I am using Crystal Reports 2008 with an Access database. I am trying to add this command to the report. See below:

SELECT T1.resulttext, T2.resulttext
FROM testresults T1 INNER JOIN testresults T2
ON T1.testdate = T1.testdate
WHERE T1.probetype = "Employee" AND T2.probetype = "Test"


The problem is that whenever I submit it, it gives me a "join expression not supported" error- anyone familiar with this error? Thanks in advance.
 
YOu are joining T1 to T1

SELECT T1.resulttext, T2.resulttext
FROM testresults T1 INNER JOIN testresults T2

ON T1.testdate = T1.testdate
-- should be ON T1.testdate = T2.testdate
WHERE T1.probetype = "Employee"
AND T2.probetype = "Test"

Ian
 
Ian,

Thanks for the response- that cleared it, but now it is saying to few parameters- expected 4.
 
Try changing the double quotes to single quotes.

-LB
 
lbass,

Thanks for the response- that got it half way there- it is now saying expected parameters 2
 
Please copy your current command into the thread.

-LB
 
lbass,

Sure- here it is:

SELECT T1.resulttext, T2.resulttext
FROM testresults T1 INNER JOIN testresults T2
ON T1.testdate = T2.testdate
WHERE T1.probetype = 'Employee' AND T2.probetype = 'Test

Thanks
 
To troubleshoot this, try adding the table twice to a new report, link the test results field and then add a record selection formula that reflects your "where" criteria. Then go to database->show SQL query and see how the query is displayed there. Then go back to your original command, and change it to mimic the query in the new report.

-LB
 
LB,

Thanks so much- I was able to get it to work.

 
It would help if you showed the final solution that worked so that other readers could benefit.

-LB
 
lbass,

It was a mistake on my part- one of the fields was in the wrong table. The probetype field was in a table that I didn't select which was why the error of missing parameters showed up.
 
And the actual final query is what? Thanks.

-LB
 
I had to change it with the new table, so it is a lot longer then the original post.


SELECT TestResults.TestId, TestResults.TestDate, TestResults.ResultText, TestMainList.probetype,T1.ResultText
FROM (TestResults INNER JOIN TestMainList ON TestResults.TestId = TestMainList.TestId) INNER JOIN
(TestResults T1 INNER JOIN TestMainList TM1 ON T1.TestId = TM1.TestId )
ON TestResults.TestId = T1.TestId
WHERE
TestMainList.probetype = "Employee" AND TM1.probetype = "Test"

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top