Professional Web Applications Themes

SQL join - Coldfusion Database Access

I am trying to join three acess tables in a recordset and trying to acess their their contents. for ex . if we have a customer table having fields id,name and another table Payment having fields id,amount. now if rst is the recordset then rst!id,rst!name,rst!amount does not work then how can I acess those values to put in some variables...

  1. #1

    Default SQL join

    I am trying to join three acess tables in a recordset and
    trying to acess their their contents. for ex . if we have
    a customer table having fields id,name and another table
    Payment having fields id,amount.

    now if rst is the recordset
    then rst!id,rst!name,rst!amount does not work
    then how can I acess those values to put in some variables
    sikhar Guest

  2. #2

    Default Re: SQL join

    Sikhar,

    Please post access questions in Access Newsgroups. You have better chances
    of getting better & accurate answers there.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: SQL join

    try rst.fields("id").value


    "sikhar" <sikharsaikiayahoo.co.in> wrote in message
    news:524a01c3418e$466dfa80$a401280aphx.gbl...
    > I am trying to join three acess tables in a recordset and
    > trying to acess their their contents. for ex . if we have
    > a customer table having fields id,name and another table
    > Payment having fields id,amount.
    >
    > now if rst is the recordset
    > then rst!id,rst!name,rst!amount does not work
    > then how can I acess those values to put in some variables

    Nikhil Patel Guest

  4. #4

    Default SQL join

    Hi, Instead of having multiple queries (see below), can I combine them into
    one big query? I need to see if a record for each user exists in each table.
    Hope you can help

    <cfquery datasource="#datasource#" name="getpd">
    SELECT * FROM personaldetails WHERE pdemail = '#GetAuthUser()#';
    </cfquery>
    <cfquery datasource="#datasource#" name="geted">
    SELECT * FROM education WHERE EDemail = '#GetAuthUser()#';
    </cfquery>
    <cfquery datasource="#datasource#" name="getjd">
    SELECT * FROM employment WHERE Eemail = '#GetAuthUser()#';
    </cfquery>
    <cfquery datasource="#datasource#" name="gets">
    SELECT * FROM skills WHERE semail = '#GetAuthUser()#';
    </cfquery>
    <cfquery datasource="#datasource#" name="ptdetails">
    SELECT * FROM ptdetails WHERE ptemail = '#GetAuthUser()#';
    </cfquery>

    Mattastic Guest

  5. #5

    Default Re: SQL join

    Do you actually need to select and use the data from each table, or are you just interested in whether or not a record exists?

    Phil
    paross1 Guest

  6. #6

    Default Re: SQL join

    Hi,

    I just need to see if the record exists.

    Thanks for your help
    Mattastic Guest

  7. #7

    Default Re: SQL join

    One way to do this would be as in the attached. You have two columns returned,
    type and type_count, and you will get 5 rows returned. If the type_count is 0
    for a row, then there are no records for that type, etc.

    Phil



    <cfquery datasource="#datasource#" name="getstuff">
    SELECT 'getpd' AS type,
    count(*) AS type_count
    FROM personaldetails
    WHERE pdemail = '#GetAuthUser()#'
    GROUP BY get_type
    UNION
    SELECT 'geted' AS type,
    count(*) AS type_count
    FROM education
    WHERE EDemail = '#GetAuthUser()#'
    GROUP BY get_type
    UNION
    SELECT 'getjd' AS type,
    count(*) AS type_count
    FROM employment
    WHERE Eemail = '#GetAuthUser()#'
    GROUP BY get_type
    UNION
    SELECT 'gets' AS type,
    count(*) AS type_count
    FROM skills
    WHERE semail = '#GetAuthUser()#'
    GROUP BY get_type
    UNION
    SELECT 'ptdetails' AS type,
    count(*) AS type_count
    FROM ptdetails
    WHERE ptemail = '#GetAuthUser()#'
    GROUP BY type
    </cfquery>

    paross1 Guest

  8. #8

    Default Re: SQL join

    Is your database normalized? Why do you need to search every field for the
    same data? The user's name should only be stored once in the database.
    Database structure is VERY important. I would first normalize your database
    before you go too far. I can recommend a few sources for you if you need some
    help.

    MarkWright Guest

  9. #9

    Default Re: SQL join

    <cfquery datasource="#datasource#" name="alldetails">
    SELECT
    *
    FROM
    personaldetails PD, education ED, employment EM, skills SKS, ptdetails PT
    WHERE
    PD.pdemail = ED.EDemail
    AND
    ED.EDemail = EM.Eemail
    AND
    EM.Eemail = SKS.semail
    AND
    SKS.semail = PT.ptemail
    </cfquery>


    Anyone, Can this be the solution?

    Tom CF Guest

  10. #10

    Default Re: SQL join

    Originally posted by: MarkWright
    Is your database normalized? Why do you need to search every field for the
    same data? The user's name should only be stored once in the database.
    Database structure is VERY important. I would first normalize your database
    before you go too far. I can recommend a few sources for you if you need some
    help.

    I'd say it is normalised cause they only seem to be ooking for the user in
    each table... so only and id would be stored in each table for that user.

    If the user is in one table are they goning to be in all or is it random for
    what tables they are in???
    Inner Join is no good for you as it will only return if the user is in all
    tables... one small amendment forthe ultiple sql statements i would make is
    rather than "SELECT *" change it to "SELECT [specific criteria]" so as to
    reduce amount of resources used by the statements.

    talon1976 Guest

  11. #11

    Default Re: SQL join

    Thanks for all your replies. I am creating a system where users can apply for
    jobs online, so I have split up all the different criteria into different
    tables. ie personal details, education details, etc. I am using their email
    address as the primary key in each table. However by doing it this way I have
    alot of queries on my pages. Am I doing this wrong? Thanks for all your help


    Mattastic Guest

  12. #12

    Default Re: SQL join

    I agree with Phil. What a great post! PK fields should never be directly
    related to data (in this case email, but sometimes db programmers use unique
    numbers like SSN). This is ALWAYS a bad idea. Data changes. A PK should be
    completely separate of the data.

    I find it helpfull to draw a huge chart of the tables, the fields they
    contain, what the fields is for, and how they relate. I refer to it often when
    writing queries. In the long run, this speeds up the process immensly.

    MarkWright Guest

  13. #13

    Default Re: SQL join

    Thats great thanks for all your help. I have changed my DB structure, so that
    when a user logs in, a unique ID is generated for a primary key and used in
    each table. Would it be good practice, to set the name attribute of the
    cfloginuser tag, to the unique ID, then use the function #GetAuthUser()#, in
    queries for data? Thanks for your help

    Mattastic Guest

  14. #14

    Default Re: SQL join

    I would use a different value for logon name than userID, so that the name
    could be changed in the future, if necessary. You would use a query to retrive
    the userID, and password etc., from the database, for the authentication
    process, then set whatever client, cookie, or session variables that are
    necessary for you to track logon state.

    Phil

    paross1 Guest

  15. #15

    Default Re: SQL join

    Thanks again. Is the cfuserlogin tag the same as a session variable? Is it
    secure enough to do : <cfloginuser name =
    '#check_user.email#,#check_user.loginID#' password ='#check_user.password#'
    roles = 'user'> Therefore I can use #listrest(GetAuthUser())# to get the
    loginid for each user, it saves having to code a new session variable.

    Mattastic Guest

  16. #16

    Default Re: SQL join

    Sorry, since I initially developed all of my applications under CF 4.5, I have
    never personally used cfloginuser, cflogin, or GetAuthUser. I ended up having
    to develop my own application authentication.

    Phil

    paross1 Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. FMS Join two flv...
    By Luiz Alberto in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 23rd, 02:51 PM
  3. Is self-join appropriate?
    By Michal Stankoviansky in forum MySQL
    Replies: 6
    Last Post: January 3rd, 08:42 PM
  4. DB2 join
    By Prakash in forum IBM DB2
    Replies: 1
    Last Post: September 10th, 05:24 PM
  5. How to Join...
    By CD in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 06:26 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