Professional Web Applications Themes

Problem with query - Coldfusion Database Access

I have created a query to select data from a number of tables. (3 tables) In summary I have 3 tables: table 1 - girls - girlid, name table 2 - rosters - rosterid, roster_date, start_time, finish_time table 3 - girl_rosters - girl_rosterid, girlid, rosterid 1) I have created a query where I can retrieve all the girl names who are on file and display the details on a web page. 2) I have created query where I can display the roster information for a particular day 3) What I do want to do is a combination of both I ...

  1. #1

    Default Problem with query

    I have created a query to select data from a number of tables. (3 tables)

    In summary I have 3 tables:

    table 1 - girls - girlid, name
    table 2 - rosters - rosterid, roster_date, start_time, finish_time
    table 3 - girl_rosters - girl_rosterid, girlid, rosterid

    1) I have created a query where I can retrieve all the girl names who are on
    file and display the details on a web page.

    2) I have created query where I can display the roster information for a
    particular day

    3) What I do want to do is a combination of both I want to display a list of
    all girls, and I want to also indicate which girls are working on the day the
    web site visitor is browsing the page.

    I have created a query which is as follows:

    SELECT dbo.girls.girlid, dbo.girls.girl_name, dbo.girls.girl_headline,
    dbo.girls.girl_thumbnail1, dbo.girls_roster.girlid ,
    dbo.girls_roster.rosterid
    FROM dbo.girls
    LEFT OUTER JOIN dbo.girls_roster
    ON dbo.girls.girlid = dbo.girls_roster.girlid
    LEFT OUTER JOIN dbo.Roster
    ON dbo.girls_roster.rosterid = dbo.Roster.RosterID
    Where dbo.girls.girl_active = 'Y' AND
    RosterDate = #CreateODBCDate(todaysdate)#
    ORDER BY 2 ASC

    I have 10 girls stored in the girl table.

    I only have 2 records in the roster table .

    I am getting returned just 2 records - the records where the girl is on the
    girl table and is also rostered to be working on a set date.

    What I want to achieve is list all the girl records and if roster records
    exist for a specific date indicate on the web page this information. I still
    however want to show all 10 records irrespective of whether a girl is rostered
    to be working.

    So I want to show basically the following:

    Alison - 18 year old student etc -
    - Click here for more info

    Bria - lovely blonde -
    - Click here for more info

    Carol - lovely redhead - Working today -
    Click here for more info

    Debbie - beautiful beach babe - -
    Click here for more info

    Any feedbacl on what needs changing in the SQL will be greatly appreciated.

    Regards

    P.S. I was under the impression Left Outer Join would pass all records etc
    whether they contained info relating to the selection criteria.





    toby007 Guest

  2. #2

    Default Re: Problem with query

    What database do you use? If Oracle for example second Left Join would kick of
    8 reecords because rosterid in both tables is Null. A solution could be first
    to update NULLs in table girls_roster with 0 and change order of tables in your
    SQL.

    CF_Oracle Guest

  3. #3

    Default Re: Problem with query

    Hi all

    I have modified the query which I thought was working OK but further testing
    revealed that is was not working properly and I cant see why it isnt working
    properly.

    Basically I have 3 tables where I am trying to get information from.

    Table 1 - Girls contains Girlid, name, pic
    Table 2 - Rosters contains rosterid, rosterdate
    Table 3 - Girls_Rosters contains girls_rostersid, girlid, rosterid

    Table 3 is really only a link table to link the two tables together.

    What I want to to is as follows:

    List all the girls on a web page. A simple select would do this. What I
    really want to do is do that but I also want to display to a person a note as
    to whether the girl is working that day - not all girls will be working on a
    given day.

    So I want to show something like this

    Girl Name Available Today


    etc

    I have a query which I developed. Whilst I get information out from this
    query - for any girl who is rostered on for today I get to records outputted
    from the query. The SQL is shown below.

    Thanks in advance for feedback

    SELECT dbo.girls.girlid, dbo.girls.girl_name, dbo.girls.girl_headline,
    dbo.girls.girl_thumbnail1, dbo.girls_roster.girlid , Abi
    Belinda X
    Bobbie
    Charlie X
    Christine X
    Debbie
    Dianne
    dbo.girls_roster.rosterid, dbo.Roster.RosterDate
    FROM dbo.girls
    LEFT OUTER JOIN dbo.girls_roster
    ON dbo.girls.girlid = dbo.girls_roster.girlid
    LEFT OUTER JOIN dbo.Roster
    ON dbo.girls_roster.rosterid = dbo.Roster.RosterID
    Where dbo.girls.girl_active = 'Y'
    ORDER BY 2 girls.girl_name

    Basically I am getting

    Abi
    Belinda
    Belinda X
    Bobbie
    Charlie
    Charlie X
    Christine
    Christine X
    Debbie
    Dianne

    Any feedback would be greatly appreciated.



    toby007 Guest

Similar Threads

  1. Query problem...
    By Martine Duval in forum MySQL
    Replies: 4
    Last Post: June 17th, 04:02 PM
  2. Query of Query problem
    By JakeFlynn in forum Macromedia ColdFusion
    Replies: 18
    Last Post: August 11th, 08:15 AM
  3. Query on Query and CF casting problem
    By obxlefty in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 18th, 12:44 PM
  4. SQL Query problem...
    By Mike Hill in forum ASP Database
    Replies: 2
    Last Post: April 1st, 11:50 PM
  5. Problem query
    By Scott Ashby in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 07:01 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