Join Type not supported- Inner Join

[jj1576]

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.

 

[IanWaterman]

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

 

[jj1576]

Ian,

Thanks for the response- that cleared it, but now it is saying to few parameters- expected 4.

 

[lbass]

Try changing the double quotes to single quotes.

-LB

 

[jj1576]

lbass,

Thanks for the response- that got it half way there- it is now saying expected parameters 2

 

[lbass]

Please copy your current command into the thread.

-LB

 

[jj1576]

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

 

[lbass]

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

 

[jj1576]

LB,

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

 

[lbass]

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

-LB

 

[jj1576]

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.

 

[lbass]

And the actual final query is what? Thanks.

-LB

 

[jj1576]

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"