try using parenthesis to group relevant parts of the FROM clause... --- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com
Toby King wrote: > I thought I had the query working but its NOT quite right. > > I have been working on a smilar query and tried building the query with a > query builder. > > Basically this query is joining 3 tables together. A table called > tbl_onshift, tbl_jobs, and tbl_serviceproviders, > > Basically I want to get all records for those people on shift (which I am > doing). I then want only one record from the jobs table for each person. I > am however getting 2 records (depending if the person is on a job). If the > person is not on a job I am getting one record for that person, but if they > are on a job I am getting the record indicating that they are on a job and > also a record indicating that they are not on a job as well. > > In the job table I have a column called bookingstatus and the booking status > can be either 2 for a current booking or 3 for a completed booking. If the > person is not on a current booking i.e. they only have records which have a > bookingstatus = 3 then I get only 1 record. If they are on a job > bookingstatus = 2 I get the record for the bookingstatus =2 and also a record > for bookingstatus = 3. How do i correct this? > > > > Here is the query > > SELECT DISTINCT > `tbl_onshift`.`girlid`, `tbl_serviceproviders`.`girlwname`, > `tbl_jobs`.`bookingstatus`, `tbl_serviceproviders`.`description` > FROM > `tbl_onshift` LEFT OUTER JOIN > `tbl_jobs` ON `tbl_onshift`.`girlid` = `tbl_jobs`.`girlid` INNER JOIN > `tbl_serviceproviders` ON `tbl_onshift`.`girlid` = > `tbl_serviceproviders`.`girlid` > ORDER BY > `tbl_serviceproviders`.`girlwname`; > > > Thanks in advance for feedback. > > Toby > > > > > > > > > > > > > > >> Hi basically the way I wanted to display the output was a follows: >> >> <span class="msgtext"> <span >> class="style5">Room Status</span></span><br> >> >> <cfset tmp1 = #qGetRooms.RecordCount#> >> >> <!--- TMP2 change the number after the division symbol "/" to reflect >> the number of times you want something to display horizontally ---> >> >> <cfset tmp2 = #tmp1#/10> >> >> <cfset tmp3 = #Round(tmp2)#+1> >> >> <cfset startpoint = "1"> >> >> <!--- ENDPOINT change the value of endpoint to match the number of >> times you want something to display horizontally ---> >> >> <cfset endpoint = "10"> >> >> <table border="0" cellspacing="5" cellpadding="5" style="border-top: >> 2px solid #660000; border-right: 2px solid #660000; border-bottom: 2px >> solid #660000; border-left: 2px solid #660000;"> >> >> <cfloop index="x" from="1" to="#tmp3#"> >> >> <tr> >> <cfloop query="qGetRooms" startrow="#startpoint#" >> endrow="#endpoint#"> >> <td> >> <cfoutput> >> <!--- <a href="<!--- index3.cfm?Trg1=Trg1&d1=#RecId# ---> "> >> ---> >> <span class="reportcontent"><cfif qGetRooms.bookingstatus eq 2><span >> style="background-color:red;color:white;"> <span >> class="style4">#qGetRooms.roomno#</span> </span> >> > > >> <cfelse> <span class="style4">#qGetRooms. >> roomno#</span> </cfif></span> >> <!--- </a> ---> >> </cfoutput></td> >> </cfloop> >> </tr> >> >> <cfset startpoint = #endpoint#+1> >> >> <!--- ENDPOINT make this iteration of the "endpoint" variable one less >> then the number you set TMP2 to be ---> >> >> <cfset endpoint = #startpoint#+9> >> </cfloop> >> </table> >> <br> >> >> So as to display rooms >> > > >> 1 2 3 4 5 6 7 8 9 10 >> 11 12 13 14 15 16 17 18 19 20 >> >> I guess I am just trying to make the application as user friendly as >> possible. >> >> >> >> >> >>> If you do a cfoutput group (room num), then you can limit the room >>> >> to >> >>> one display per room number. Use the columns in the booked table to >>> >>> determine if the room should be highlighted. >>> >>> Also, a booked room is usually determined by a date and time of the >>> >>> booking. If this is true with you system, you will need to add date >>> >>> and time to your queries. >>> >>> william >>> >>> William Seiter (mobile) >>> >>> Have you ever read a book that changed your life? >>> go to: http://www.winninginthemargins.com >>> and use passcod: GoldenGrove >>> >>> -----Original Message----- >>> From: "Toby King" <[EMAIL PROTECTED]> >>> To: "CF-Talk" <[email protected]> >>> Sent: 1/28/2008 2:05 PM >>> Subject: Help witha query >>> >>> Hi there >>> >>> I have 2 tables in A MySQL database. A room table and a room >>> occupancy table which stores records for a selected period of time. >>> >>> What I want to do is display all rooms (rooms 1-20) along with the >>> status of the room. >>> >>> Currently my query is as follows: >>> >>> select distinct tbl_rooms.roomno, tbl_jobs.bookingstatus >>> from tbl_rooms left join tbl_jobs >>> on tbl_rooms.roomid = tbl_jobs.roomid >>> order by roomno >>> >>> What I am getting is the following displayed: >>> >>> Rooms >>> >>> 1 1 2 2 3 4 5 6 7 8 >>> >>> Basically I have the following in my tables - I have one record for >>> >>> each room in the rooms table and multiple records in the tbl_jobs >>> table. >>> >>> Room 1 has multiple records where the booking has been completed and >>> >>> one record showing that the room is currently booked. The same for >>> >>> room 2. Room 3 has only been booked before and there are no current >>> >>> bookings for room 3. >>> >>> Room 4 has never been booked before but shows up as it is a room in >>> >>> the Room table. Room 5 has a number of completed bookings and is >>> currently >>> >> booked >> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297628 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
