Help! Complex query

[RuBot]

I have two queries that both work fine.
3 Tables: TICKET, TECHS, NOTE
1. A query that checks a ticket from TICKET for due dates and gets info from
the TECHS.
2. A query that gets ticket info. from TICKET and notes (if any) from NOTES

What I am trying to do is combine them!

Query 1
<cfquery datasource="#db2#"name="reminder">
SELECT a.Entry_ID,a.entry_date,a.entry_description,a.emai lto,a.remind,b.email
FROM TICKET A INNER JOIN TECHSl B on a.name=b.Name
WHERE a.duedate=<cfqueryparam cfsqltype="cf_sql_date" value="#datenow#">
AND a.status=<cfqueryparam cfsqltype="cf_sql_varchar" value="Open">
AND a.remind=<cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
</cfquery>

Query 2
<CFQUERY NAME="ticket_info" datasource="#db#">
SELECT *
FROM TICKET a left join NOTES b on a.entry_id = b.ticket_id
WHERE a.Entry_ID = #ticketID#
ORDER BY b.Notes_Date DESC
</CFQUERY>

What I've tried:
<cfquery datasource="#db2#"name="reminder">
SELECT
a.Entry_ID,a.entry_date,a.entry_description,a.emai lto,a.remind,b.email,c.notes_d
ate,c.notes
FROM TICKET a (inner join TECHS B on a.name=b.name (left join NOTES c on
a.entry_id=c.ticket_id))
WHERE a.duedate='#datenow#'
AND a.status='Open'
AND a.remind='Yes'
order by c.notes_date desc
</cfquery>

<cfquery datasource="#db2#"name="reminder">
SELECT
a.Entry_ID,a.entry_date,a.entry_description,a.emai lto,a.remind,b.email,c.notes_d
ate,c.notes
FROM TICKET a, TECHS B
WHERE a.name=b.name and a.duedate=<cfqueryparam cfsqltype="cf_sql_date"
value="#datenow#">
AND a.status=<cfqueryparam cfsqltype="cf_sql_varchar" value="Open">
AND a.remind=<cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
left join (NOTES c on a.entry_id=c.ticket_id order by c.notes_date desc
</cfquery>

Please help, I'm not sure if it's structuring or syntax that is preventing me
from getting this right...

[Dan Bracuk]

Your 2nd attempt is on the right track. You just don't have your code in the
right order. Try something resembling:

select fields
from ticket a inner join techs b on something
left join notes c on something

where etc

[RuBot]

This:
<cfquery datasource="#db2#"name="reminder">
SELECT
a.Entry_ID,a.entry_date,a.entry_description,a.emai lto,a.remind,b.email,c.notes_d
ate,c.notes
FROM #tkt# a inner join TechsTbl B on a.name=b.name left join #notes# c on
a.entry_id=c.ticket_id
WHERE a.duedate=<cfqueryparam cfsqltype="cf_sql_date" value="#datenow#">
AND a.status=<cfqueryparam cfsqltype="cf_sql_varchar" value="Open">
AND a.remind=<cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
order by c.notes_date desc
</cfquery>

Produces:
Error Executing Database Query.
Syntax error (missing operator) in query expression 'a.name=b.name left join
NotesTbl c on a.entry_id=c.ticket_id'.

The error occurred in
C:\Inetpub\wwwroot\corporate\Systems\SystemsOnly\H elpDeskBeta\tickle.cfm: line
29

27 : WHERE a.duedate=<cfqueryparam cfsqltype="cf_sql_date" value="#datenow#">
28 : AND a.status=<cfqueryparam cfsqltype="cf_sql_varchar" value="Open">
29 : AND a.remind=<cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
30 : order by c.notes_date desc
31 : </cfquery>




--------------------------------------------------------------------------------

SQL SELECT
a.Entry_ID,a.entry_date,a.entry_description,a.emai lto,a.remind,b.email,c.notes_d
ate,c.notes FROM Help_Desk_List a inner join TechsTbl B on a.name=b.name left
join NotesTbl c on a.entry_id=c.ticket_id WHERE a.duedate= (param 1) AND
a.status= (param 2) AND a.remind= (param 3) order by c.notes_date desc

[Dan Bracuk]

take the octothorps out of your from clause.

[RuBot]

I think you mean this...
<cfquery datasource="#db2#"name="reminder">
SELECT
a.Entry_ID,a.entry_date,a.entry_description,a.emai lto,a.remind,b.email,c.notes_d
ate,c.notes
FROM #tkt# a inner join TechsTbl B on a.name=b.name left join #notes# c on
a.entry_id=c.ticket_id
WHERE a.duedate='#datenow#'
AND a.status='Open'
AND a.remind='Yes'
order by c.notes_date desc
</cfquery>


Error Executing Database Query.
Syntax error (missing operator) in query expression 'a.name=b.name left join
NotesTbl c on a.entry_id=c.ticket_id'.

The error occurred in
C:\Inetpub\wwwroot\corporate\Systems\SystemsOnly\H elpDeskBeta\tickle.cfm: line
27

25 : SELECT
a.Entry_ID,a.entry_date,a.entry_description,a.emai lto,a.remind,b.email,c.notes_d
ate,c.notes
26 : FROM #tkt# a inner join TechsTbl B on a.name=b.name left join #notes# c
on a.entry_id=c.ticket_id
27 : WHERE a.duedate='#datenow#'
28 : AND a.status='Open'
29 : AND a.remind='Yes'

[Dan Bracuk]

To trouble shoot this, take baby steps. Keep adding steps until the error
occurs.

Step 1.
select a.entry_id
from Help_Desk_List a inner join TechsTbl B on a.name=b.name

Step 2
select a.entry_id
from Help_Desk_List a left join NotesTbl c on a.entry_id=c.ticket_id

Step 3
select a.entry_id
from Help_Desk_List a inner join TechsTbl B on a.name=b.name
left join NotesTbl c on a.entry_id=c.ticket_id

Continue to add more fields, and then your where clause until it crashes.
Then you will know what caused it to crash.