Professional Web Applications Themes

CFQUERY - Coldfusion - Advanced Techniques

I attached the code. I am trying to query and output the games. What it is doing there is taking all the games from the games table (g) and compare it with the locations table (l) which works fine. So then after that it takes all the games home team ids and matches them within the game table where that id is present. Then it does the visiting team. Problem is when it outputs the data it only does one team so you get half the entries you should. I think that's how I should explain how it is supposed ...

Sponsored Links
  1. #1

    Default CFQUERY

    I attached the code. I am trying to query and output the games. What it is
    doing there is taking all the games from the games table (g) and compare it
    with the locations table (l) which works fine. So then after that it takes all
    the games home team ids and matches them within the game table where that id is
    present. Then it does the visiting team. Problem is when it outputs the data it
    only does one team so you get half the entries you should. I think that's how I
    should explain how it is supposed to work. You will probably see what I mean by
    looking at the code. Any help would be great.

    <cfquery name="allgames" username="#username#" password="#pass#"
    datasource="#datasource#">
    SELECT g.*, l.*, t.*
    FROM games g, location l, team t
    WHERE g.loc_id = l.loc_id and g.hteam = t.team_id and g.vteam = t.team_id
    </cfquery>

    <table width="100%" border="1">
    <tr>
    <td colspan="7" class="leaguename">Game Update / Edit </td>
    </tr>
    <tr>
    <td width="10%" class="smallbold">Date</td>
    <td width="10%" class="smallbold">Time</td>
    <td width="14%" class="smallbold">Location</td>
    <td width="25%" class="smallbold">Home Team </td>
    <td width="25%" class="smallbold">Visiting Team </td>
    <td width="8%" class="smallbold">Edit</td>
    <td width="8%" class="smallbold">Delete</td>
    </tr>
    <cfoutput query="allgames">
    <tr>
    <td width="10%" class="smallbold">#date#</td>
    <td width="10%" class="smallbold">#time#</td>
    <td width="14%" class="smallbold">#fac# ----- #field#</td>
    <td width="25%" class="smallbold">#hteam#</td>
    <td width="25%" class="smallbold">#vteam#</td>
    <td width="8%" class="smallbold">edit</td>
    <td width="8%" class="smallbold">delete</td>
    </tr>
    </cfoutput>

    Sponsored Links
    weswhite7 Guest

  2. #2

    Default Re: CFQUERY

    How do your three tables relate to each other? What are the fields in the
    games table?

    Originally posted by: weswhite7
    I attached the code. I am trying to query and output the games. What it is
    doing there is taking all the games from the games table (g) and compare it
    with the locations table (l) which works fine. So then after that it takes all
    the games home team ids and matches them within the game table where that id is
    present. Then it does the visiting team. Problem is when it outputs the data it
    only does one team so you get half the entries you should. I think that's how I
    should explain how it is supposed to work. You will probably see what I mean by
    looking at the code. Any help would be great.



    Dan Guest

  3. #3

    Default Re: CFQUERY

    You will need to include the team table twice in your query. Something like
    this:

    <cfquery name="allgames" username="#username#" password="#pass#"
    datasource="#datasource#">
    SELECT g.*, l.*, t.*
    FROM games g, location l, team t, team v
    WHERE g.loc_id = l.loc_id and g.hteam = t.team_id and g.vteam = v.team_id
    </cfquery>

    In your original query, t.team_id can have only one value per returned record
    from the query; not one for the home team and a different value for the visitor.


    randomfactor Guest

  4. #4

    Default Re: CFQUERY

    Oops.

    that select line should have changed to
    SELECT g.*, l.*, t.*, v.*

    But I'll bet you already figured that out.

    randomfactor Guest

  5. #5

    Default Re: CFQUERY

    Actually, the select line should have been changed to individual fieldnames,
    with aliases as applicable, instead of wildcards.

    Originally posted by: randomfactor
    Oops.

    that select line should have changed to
    SELECT g.*, l.*, t.*, v.*

    But I'll bet you already figured that out.




    Dan Guest

  6. #6

    Default Re: CFQUERY

    thank you. that worked like a charm...
    weswhite7 Guest

  7. #7

    Default Re: CFQUERY

    weswhite7 sez:

    "hey, so now that i have that working, i can get both team numbers. now how do
    i get two different team names that correspond to those numbers from the teams
    database?"

    I don't know what your actual column names are in your teams table. Suppose
    the name of the team is in the 'team_name' column. Then your query would go
    something like this:

    SELECT g.*, l.*, t.team_name hometeam, v.team_name visitor
    FROM games g, location l, team t, team v
    WHERE g.loc_id = l.loc_id and g.hteam = t.team_id and g.vteam = v.team_id

    The team name columns in the result will be named "hometeam" and "visitor"
    instead of t.team_name and v.team_name.


    randomfactor Guest

  8. #8

    Default Re: CFQUERY

    again, thank you very much...

    im somewhat new to the whole more dynamic query area.. but thanks
    weswhite7 Guest

Similar Threads

  1. Possible cfquery bug?
    By hans in forum Coldfusion Database Access
    Replies: 16
    Last Post: May 15th, 10:32 PM
  2. cfquery bug...still?
    By jlerath in forum Coldfusion Server Administration
    Replies: 2
    Last Post: November 30th, 06:06 PM
  3. cfquery and T-SQL
    By doug777 in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: March 17th, 10:01 AM
  4. cfquery
    By ducman in forum Macromedia ColdFusion
    Replies: 3
    Last Post: August 2nd, 07:09 AM
  5. need help with cfquery
    By mkauspe in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 24th, 11:11 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