Professional Web Applications Themes

Help! Complex query - Coldfusion Database Access

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 ...

  1. #1

    Default Help! Complex query

    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...





    RuBot Guest

  2. #2

    Default Re: Help! Complex query

    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

    Dan Bracuk Guest

  3. #3

    Default Re: Help! Complex query

    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




    RuBot Guest

  4. #4

    Default Re: Help! Complex query

    take the octothorps out of your from clause.
    Dan Bracuk Guest

  5. #5

    Default Re: Help! Complex query

    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'




    RuBot Guest

  6. #6

    Default Re: Help! Complex query

    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.


    Dan Bracuk Guest

Similar Threads

  1. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  2. Presenting complex query results through datagrid
    By Bijoy Naick in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 24th, 08:19 PM
  3. Adding/Updating records on form with complex query
    By Steve Marsden in forum Microsoft Access
    Replies: 0
    Last Post: July 28th, 10:07 AM
  4. Fairly Complex Query
    By Mike Davies in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 06:19 PM
  5. A complex query
    By Venkatesan M in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 02:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139