Professional Web Applications Themes

Output from different datasource in cfquery statement - Coldfusion Server Administration

i've make 2 cfquery statements. one refers to datasource DatStudent and another one refers to datasource DatModul. in coldfusion admin, DatStudent refers to stdn.mdb and DatModul refers to modul.mdb. primary key in stdn.mdb is id_stdn but it was a foreign key in modul.mdb. how can i make the information output from these datasources?...

  1. #1

    Default Output from different datasource in cfquery statement

    i've make 2 cfquery statements. one refers to datasource DatStudent and another
    one refers to datasource DatModul. in coldfusion admin, DatStudent refers to
    stdn.mdb and DatModul refers to modul.mdb. primary key in stdn.mdb is id_stdn
    but it was a foreign key in modul.mdb. how can i make the information output
    from these datasources?

    Ryunosuke Guest

  2. #2

    Default Re: Output from different datasource in cfquerystatement

    Is this an idea?
    (if id_stdn is string type, you would of course use
    #QuotedValueList(q1.id_stdn)#)

    <cfquery name="q1" datasource="DatStudent">
    select student, id_stdn
    from studentTable
    where student in ('Peter','Paul','Mary')
    </cfquery>
    <cfquery name="q2" datasource="DatModul">
    select modules
    from moduleTable
    where id_stdn in (#ValueList(q1.id_stdn)#)
    </cfquery>




    BKBK Guest

  3. #3

    Default Re: Output from different datasource in cfquery statement

    you can use two different dsn in one cfquery if you use the path to the
    mdb file and join the tables:

    SELECT P.TRANSACTION_DATE, P.PAYMENT_TYPE, SU.UserFirstName & ' ' &
    SU.UserLastName AS UserFullName, P.USER_ID
    FROM [path to .mdb file].Users SU INNER JOIN RESV P ON SU.UserID = P.USER_ID

    so maybe for you:

    select S.student, S.id_stdn, M.modules
    from studentTable S INNER JOIN ['path to modul.mdb'].moduleTable M ON
    S.id_stdn = M.id_stdn
    where S.student in ('Peter','Paul','Mary')

    HTH
    --
    Tim Carley
    [url]www.recfusion.com[/url]
    [email]infoNOSPAMINGrecfusion.com[/email]
    Mountain Lover Guest

  4. #4

    Default Re: Output from different datasource in cfquerystatement

    use the path to the mdb file and join the tables
    It is of course okay to use the path. However, I avoid using it because if
    anyone was to move the MDB files, it would then mean searching through a lot of
    CFML pages to modify the filepaths in the queries. It's better to have all the
    settings set or modified in just one place, the Coldfusion Administrator.

    Also, there could be problems when using the join syntax with MSAccess in
    Coldfusion. Something like this wouldn't cause any problems. (In fact you can
    test the example directly, because I'm using the MSAccess database samples that
    come with Coldfusion)

    <cfquery name="q" datasource="cfbookclub">
    select authors.bio, authors.lastname, artists.lastname, artists.email
    from .authors,
    .artists
    where authors.firstname=artists.firstname
    </cfquery>
    <cfdump var="#q#">



    BKBK Guest

Similar Threads

  1. output of a <cfquery>
    By cfcypher in forum Macromedia ColdFusion
    Replies: 3
    Last Post: June 9th, 06:23 PM
  2. Error executing a cfquery update statement. PLEASE HELP
    By murpg in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: April 28th, 02:54 AM
  3. cfquery insert into sql statement
    By maddiel in forum Macromedia ColdFusion
    Replies: 4
    Last Post: April 27th, 08:53 PM
  4. IF statement within CFQUERY/SQL
    By Unamailer in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 22nd, 01:18 PM
  5. Output from Cfquery
    By mike in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 29th, 04:52 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