inner join select

[Guest]

can someone help me with this query, i am getting errors, i am not sure how to add a select function to the on clause.

could someone please advise what i am doing wrong

<CFQUERY datasource="#application.ds#" Name="GetSchH2">
SELECT *
FROM SMS_Players_Table

INNER JOIN appoint_table
ON ServerSMS <= #CreateODBCDateTime(Now())# OR ServerSMS <> ''

INNER JOIN SMS_Clubs_Table
ON appoint_table.LoginID = SMS_Clubs_Table.ClubID AND Country = 'Australia'

INNER JOIN (SELECT SUM(Cost)as TotalCost FROM SMS_Records) ON SMS_Records.ClubID = SMS_Clubs_Table.ClubID

WHERE appoint_table.App_ClientID = SMS_Players_Table.PlayerID AND appoint_table.HCODE = 'H2'
</cfquery>

[Dan_Bracuk]

At least one of your problems is here:
INNER JOIN appoint_table
ON ServerSMS <= #CreateODBCDateTime(Now())# OR ServerSMS <> ''

The first part suggests ServerSMS is a timestamp or datetime field and the second part suggests that it is text. At least one of those is wrong.

[BKBK]

Wouldn't it be simpler to do something like this for suitable columns W, X, Y, Z, etc. ?

[The_ScareCrow]

While one problem is as Dan said, another is

INNER JOIN (SELECT SUM(Cost)as TotalCost FROM SMS_Records) ON SMS_Records.ClubID = SMS_Clubs_Table.ClubID


Should be

INNER JOIN (SELECT SUM(Cost)as TotalCost, ClubID FROM SMS_Records GROUP BY ClubID) As subTable ON subTable.ClubID = SMS_Clubs_Table.ClubID

Note that you need to alias the sub select to join to the SMS_Clubs table, and because you need the clubid to join you have to return this in the select clause, thus you then have to group the SUM by the clubid.

Ken