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
@houseoffusion.com
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
-Original Message-
From: Toby King [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
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
You are going to want to use a LEFT OUTER JOIN.
The easiest way to get your head around how joins work is to use a query
builder. Toad for Mysql is a good app for this. MS access will work as well.
On Jan 28, 2008 5:05 PM, Toby King [EMAIL PROTECTED] wrote:
Hi there
I have 2 tables in A
@houseoffusion.com
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
life?
Go to: www.winninginthemargins.com
Enter passkey: goldengrove
Web Developer
http://William.Seiter.com
-Original Message-
From: Toby King [mailto:[EMAIL PROTECTED]
Sent: Monday, January 28, 2008 2:39 PM
To: CF-Talk
Subject: Re: Help witha query
Hi basically the way I wanted
PROTECTED]
Sent: Monday, January 28, 2008 5:15 PM
To: CF-Talk
Subject: Re: Help witha query
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
that changed your life?
go to: http://www.winninginthemargins.com
and use passcod: GoldenGrove
-Original Message-
From: Toby King [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: 1/28/2008 2:05 PM
Subject: Help witha query
Hi there
I have 2 tables in A MySQL
If all your photos will have prices you won't need to left join you can
just join on them.
Joel Watson wrote:
Joe,
First of all I am not sure if you need LEFT JOIN for both comments and
prices. My first example was based on the assumption that you could have
photos without comments and
If all your photos will have prices you won't need to left join you can
just join on them.
Joel Watson wrote:
That's true, but when I combine it with the LEFT OUTER JOIN on the comments, it
screws up the comments. While it will return the prices information fine, it
duplicates the
If all your photos will have prices you won't need to left join you
can
just join on them.
Joel Watson wrote:
That's true, but when I combine it with the LEFT OUTER JOIN on the
comments, it screws up the comments. While it will return the prices
information fine, it
I have two tables, photos and comments. Their structures are as follows:
photos table
--
photoID
photoTitle
comments table
--
commentID
commentText
photoID_fk
I need to write a query that will allow me to display all the records in photos
while also displaying
First write your sql as
SELECT p.photoID, p.photoTitle, c.commentId, c.commentText
FROMphotos p
LEFT OUTER JOIN comments c
ON c.photoid_fk = p.photoid
ORDER BY p.photoID, c.commentID
Then output the query using cfoutput and group like this
ul
cfoutput
First write your sql as
SELECT p.photoID, p.photoTitle, c.commentId, c.commentText
FROMphotos p
LEFT OUTER JOIN comments c
ON c.photoid_fk = p.photoid
ORDER BY p.photoID, c.commentID
Then output the query using cfoutput and group like this
ul
cfoutput
First write your sql as
SELECT p.photoID, p.photoTitle, c.commentId, c.commentText
FROMphotos p
LEFT OUTER JOIN comments c
ON c.photoid_fk = p.photoid
ORDER BY p.photoID, c.commentID
Then output the query using cfoutput and group like this
ul
cfoutput
Joe,
First of all I am not sure if you need LEFT JOIN for both comments and
prices. My first example was based on the assumption that you could have
photos without comments and you might want to display those as well.
It will help if you can paste the entire query and your output code.
Thanks
Joe,
First of all I am not sure if you need LEFT JOIN for both comments and
prices. My first example was based on the assumption that you could have
photos without comments and you might want to display those as well.
It will help if you can paste the entire query and your output code.
Thanks
I have a table that contains user and station information, as well as a boolean
field for whether the information is displayed or not.
It looks roughly like this:
uStationID
userID_fk
stationID_fk
checked
I have another very similar table that looks like this
cStationID
customerID_fk
As an illustration, say that users table has the following entries:
userID_fk [1]
stationID_fk [4]
checked [0]
So here, the query will return only the value of the second and forth
row.
The forth row isn't checked. I'm assuming that's just a typo.
If I'm understanding
As an illustration, say that users table has the following entries:
userID_fk [1]
stationID_fk [4]
checked [0]
So here, the query will return only the value of the second and
forth
row.
The forth row isn't checked. I'm assuming that's just a typo.
If I'm
No magic involved. But glad you'll be alive to enjoy the weekend ;-)
~|
Create robust enterprise, web RIAs.
Upgrade integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
I haven't been able to find a good SQL forum that get's responses like here on
CF-Talk.
So I'm sorry, skip over this message is it urks you that I posted this. Sorry.
I'm trying to figure out how the average rating (dbo.recipereviews.ratingID)
where the
average is greater than or equal to 4
Doug,
Rick Root posted a thread which reminded me of the issue you are having. He's
talking about a way to optimize a zip-lookup function perhaps similar to yours.
That post is at:
http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50216
Hope this is of some help. Seems to me
Ok, cannot seem to get this to work. I get a stack overflow due to the IN
clause in the query. Microsoft says this is an issue with SQL2K and I need
to do the following...
!---MS STUFF---
CREATE TABLE #IN_values (au_id char(4))
INSERT INTO #IN_values select au_id FROM
Hey Doug,
Not sure what is exactly happening, but what I would recommend is first,
capture the output SQL from your CF.
To do this, take all the SQL and put it into a CFSAVECONTENT
Then dump that out and try using it in SQL Query Analyzer. It should help
narrow down where the issue is.
--
Alan
Well, this is the issue according to Microsoft.
http://support.microsoft.com/kb/288095
Doug B.
- Original Message -
From: Alan Rother [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tuesday, February 06, 2007 5:03 PM
Subject: Re: Help with query please.
Hey Doug
]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tuesday, February 06, 2007 5:03 PM
Subject: Re: Help with query please.
Hey Doug,
Not sure what is exactly happening, but what I would recommend is first,
capture the output SQL from your CF.
To do this, take all the SQL and put
Well here is the offending code. Some of it is quite long especially the zip
locator, so I will attempt to shorten it for the lists sake. Maybe someone
can show me the best ways to handle this.
First the zip locator runs and selects all zip codes that are within the
specified criteria. 0-500
I have a database containing a list of events (birthdays,
anniversaries etc), and want to produce a listing of the events
relating to this week, this month, today etc as the user requests on
the form I give them.
Because not everyone's birth year is known, not all the birthdays have
a year. they
assuming you have two date variables.. startDate and endDate..
WHERE (day #day(startDate)#)
AND (month #month(startDate)#)
AND (day #day(endDate)#)
AND (month #month(endDate)#)
Mike Kear wrote:
I have a database containing a list of events (birthdays,
anniversaries etc), and want
On Wednesday 26 May 2004 19:07 pm, Alexander Sherwood wrote:
I'm not aware of any restrictions on the data types a CF query can contain.
Am I wrong?
It would be restricted to valid SQL types, wouldn't it ?
How the hell do you get a CFC into a query column to run a QoQ on anyway ? I
think you
Has anyone run into errors when doing a Query of a Query where one of the columns holds a CFC?
I'm not aware of any restrictions on the data types a CF query can contain. Am I wrong?
Thanks!
--
Alex
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Jake. wrote:
SELECT
AVG(r.rating) AS rating,
r.LinkIDFK2
FROM
Reviews r INNER JOIN Traffic t
ON r.LinkIDFK2 = t.LinkIDFK
GROUP BY
LinkIDFK2
ORDER BY
AVG(r.rating) DESC,
t.Hits DESC
Sorry, same problem:
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access
OK, I'm trying to write a hefty query and having a bit of trouble.
I'm trying to find the highest rated items (max of 10 and based on an
average of all the times that item was rated). Since many of the top
rated items will have the same rating, and I need to choose the top
10, I further want to
Jake McKee wrote:
I'm trying to find the highest rated items (max of 10 and based on an
average of all the times that item was rated). Since many of the top
rated items will have the same rating, and I need to choose the top
10, I further want to base it on traffic to that item.
So said
SELECT
AVG(r.rating) AS rating,
r.LinkIDFK2
FROM
Reviews r INNER JOIN Traffic t
ON r.LinkIDFK2 = t.LinkIDFK
GROUP BY
LinkIDFK2
ORDER BY
rating DESC,
t.Hits DESC
Depending on your database you can usually limit the numer of
results by using LIMIT or TOP.
I'm currently using Access. I
Jake. wrote:
SELECT
AVG(r.rating) AS rating,
r.LinkIDFK2
FROM
Reviews r INNER JOIN Traffic t
ON r.LinkIDFK2 = t.LinkIDFK
GROUP BY
LinkIDFK2
ORDER BY
rating DESC,
t.Hits DESC
Depending on your database you can usually limit the numer of
results by using LIMIT or TOP.
Jake. wrote:
SELECT
AVG(r.rating) AS rating,
r.LinkIDFK2
FROM
Reviews r INNER JOIN Traffic t
ON r.LinkIDFK2 = t.LinkIDFK
GROUP BY
LinkIDFK2
ORDER BY
rating DESC,
t.Hits DESC
Depending on your database you can usually limit the numer of
results by using LIMIT
Hi,
I have a query that I need to use to search a text field in a table. The text strings
that are in the table in this field sometimes include a pound sign (#). Whenever I
the query is run using a variable that includes a pound sign it returns no records.
Here is an abbreviated example of
Please disregard my message as I have figured out my problem.
Thanks,
Eric Homa
- Original Message -
From: Eric Homa
To: [EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 1:49 PM
Subject: Need Help With Query
Hi,
I have a query that I need to use to search a text field in a table
.
-Original Message-
From: Eric Homa [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 3:50 PM
To: CF-Talk
Subject: Need Help With Query
Hi,
I have a query that I need to use to search a text field in a
table. The text strings that are in the table in this field
Sent: Sunday, March 31, 2002 8:25 PM
Subject: Re: Help With Query
Can't pull out count for each individual FishID. I tried grouping using the
Group attribute in CFQUERY and the grouping of course works, but all I'm
missing is the SUM for each group.
Is there a function to sum items
Sorry. my mistake. I needed to change your t.id with my t.fishid.
Many thanks for all your help. All this on April Fool's day!
- Original Message -
From: Dina Hess [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 01, 2002 8:37 AM
Subject: Re: Help With Query
I have a fish databse. One table contains a Fish Database called FishDB. The other
stores the fish per tank FishINV. So if I add three of the same type of fish I get
three records in FISHINV records. I did it this way so I can add and remove fish and
keep details about each fish. They may have
, COUNT, etc.
Hope that helps.
Dina
- Original Message -
From: Frank Mamone
To: CF-Talk
Sent: Sunday, March 31, 2002 11:15 AM
Subject: Help With Query
I have a fish databse. One table contains a Fish Database called FishDB. The other
stores the fish per tank FishINV. So
answer. I'll take a closer look and see if it helps me.
Frank
- Original Message -
From: Dina Hess [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, March 31, 2002 12:57 PM
Subject: Re: Help With Query
Frank,
This definitely sounds kinda fishy. ;)
So let's see...you've
Frank,
Illuminate me. Just out of curiousity, what type of data might you store in the main
and sub fish categories?
Dina
- Original Message -
From: Frank Mamone
To: CF-Talk
Sent: Sunday, March 31, 2002 12:17 PM
Subject: Re: Help With Query
Hi Dina,
This is what I
PROTECTED]
Sent: Sunday, March 31, 2002 1:25 PM
Subject: Re: Help With Query
Frank,
Illuminate me. Just out of curiousity, what type of data might you store
in the main and sub fish categories?
Dina
- Original Message -
From: Frank Mamone
To: CF-Talk
Sent: Sunday, March
: Sunday, March 31, 2002 12:39 PM
Subject: Re: Help With Query
OK. Here's an example:
FishDB Id CategoryID SubCategoryID SciName CommonName Comment
52 1 1 Anomalochromis thomasi African Butterfly Cichlid
Id CategoryID SubCategoryID SciName CommonName Comment
25 1 1
).
- Original Message -
From: Dina Hess
To: CF-Talk
Sent: Sunday, March 31, 2002 1:53 PM
Subject: Re: Help With Query
Frank,
Try this:
SELECT count(t.id), f.CommonName, s.FishSubCategory
FROM FishDB AS f, FishInv AS t, FishSubCategory AS s
WHERE f.id=t.id And f.id
]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, March 31, 2002 3:38 PM
Subject: Re: Help With Query
Whoops! You'll need to change that query slightly to pull your count
out...
First line should be:
Select count(t.id) as FishCount,...
And if you have a *lot* of fish in an MS Access db, you'll
SELECT com.typeID,...,SUM(com.sysPrice) AS Price,...
- Original Message -
From: Douglas L. Brown
To: CF-Talk
Sent: Friday, November 30, 2001 12:34 PM
Subject: OT: help w/ query
I have the following query that I need to get the sum of the sysPrice
from. I keep getting
Thanks Dina, I decided to use the ol query of query and bingo problem solved
Doug
- Original Message -
From: Dina Hess [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Saturday, December 01, 2001 11:40 AM
Subject: Re: help w/ query
SELECT com.typeID,...,SUM(com.sysPrice
I have the following query that I need to get the sum of the sysPrice
from. I keep getting an the ol aggregate function or group by error, can
someone help please?
SELECT com.typeID,
com.partNum,
com.description,
com.currentType,
com.salePrice,
com.sysPrice,
parts.isDefault,
I think every column in your SELECT statement must be present in your
GROUP by clause (except for aggregate functions).
Jamie
On Fri, 30 Nov 2001 10:34:55 -0800, in cf-talk you wrote:
I have the following query that I need to get the sum of the sysPrice
from. I keep getting an the ol
Ok I will lay this out best i can... I have a database I am dealing just
with one small table.
In that table we have:
Auto_ID FirstName LastName SocialSecNum TestName TestDate
If someone takes a test, we will use Driver Training as an example. They
take
the test and pass and their info is
Can't you say something like
SELECT name, max(date)
FROM table
WHERE date@mydate-2years
GROUP BY name
Jason Powers
Fig Leaf Software
-Original Message-
From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 02, 2000 11:51 AM
To: CF-Talk
Subject: Help with Query
Ok
To: CF-Talk
Subject: RE: Help with Query
Can't you say something like
SELECT name, max(date)
FROM table
WHERE date@mydate-2years
GROUP BY name
Jason Powers
Fig Leaf Software
-Original Message-
From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 02
maybe you can shed some light got it to work by doing this: (there could be
an easier way)
This query finds all the distinct ssn's.
CFQUERY DATASOURCE="#DSN#" name="search"
SELECT DISTINCT ssn
FROM STUDENTS
WHERE AIRPORT_CODE = 'AAE'
AND testname = 'Driver
: Thursday, November 02, 2000 1:48 PM
To: CF-Talk
Subject: Still need Help with Query
maybe you can shed some light got it to work by doing this: (there could be
an easier way)
This query finds all the distinct ssn's.
CFQUERY DATASOURCE="#DSN#" name="search"
1.) CFQUERY Name="Tech" DataSource="#DSNCCare#"
select *
from nxsteche
where tech_code = '#tech#'
/CFQUERY
2.) CFQUERY Name="CASES" DataSource="#DSNCCare#"
SELECT DISTINCT nxstar_e.tar_num, nxstar_e.bus_name,
nxstar_e.cust_code, nxstar_e.tar_status,
Max(nxsprodr.prod_desc) AS prod_desc,
ra: [EMAIL PROTECTED]
Asunto: round 3: HELP with Query.
1.) CFQUERY Name="Tech" DataSource="#DSNCCare#"
select *
from nxsteche
where tech_code = '#tech#'
/CFQUERY
2.) CFQUERY Name="CASES" DataSource="#DSNCCare#"
SELECT DISTINCT nxstar
It seems that field "nxstar_e.assigned_to" is numeric type (like
'#cookie.user_id#'), and '#tech' is char.
Nope. Both are type char.
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe
1.) CFQUERY Name="Tech" DataSource="#DSNCCare#"
select *
from nxsteche
where tech_code = '#tech#'
/CFQUERY
2.) CFQUERY Name="CASES" DataSource="#DSNCCare#"
SELECT DISTINCT nxstar_e.tar_num, nxstar_e.bus_name, nxstar_e.cust_code,
nxstar_e.tar_status, Max(nxsprodr.prod_desc) AS prod_desc,
|However doing this:
|input type="checkbox" name="runway_id#runway_id#"
|value="#runway_id#" CFIF
|runway_ID IS "qgetrunwaydetail.runway_ID"CHECKED/CFIF#runway#
|
|Does not work. even though there are 2 runway Id's that are
|pulled from the
|runwaydetail query it only brings back one
|in this
Hi Ben,
Try
SELECT Email FROM
CustomerLeads
WHERE CustomerLeads.Email ='#Email#'
UNION
SELECT Email FROM
EuropeLeads
WHERE EuropeLeads.Email ='#Email#'
This or a variation on this should work.
Regards,
Anthony Geoghegan
Lead Developer
Ireland Film and Television Net
26 South Frederick Street
Ok I run these 2 queries:
CFQUERY datasource="#dsn#" name="qgetrunways"
SELECT *
FROM Runways
/CFQUERY
CFQUERY datasource="#dsn#" name="qgetrunwaydetail"
SELECT *
FROM Runway_Detail
WHERE O_Report_Num = #qgetops.O_Report_Num#
/CFQUERY
The first just pulls all the runways.
The 2nd pulls any
July 20, 2000 1:57 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Help w/ query display...
Ok I run these 2 queries:
CFQUERY datasource="#dsn#" name="qgetrunways"
SELECT *
FROM Runways
/CFQUERY
CFQUERY datasource="#dsn#" name="qge
t Development Coordinator
AAAE
703.578.2509
[EMAIL PROTECTED]
http://www.airportnet.org
---
-Original Message-
From: Kelly Matthews [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, July 20, 2000 2:01 PM
To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE:
AS far as HTML is concerned the syntax for a CHECKED radio button or check
box is;
input type="radio" name="name" value="value" checked
input type="checkbox" name="name" value="value" checked
you have your 'checked' argument outside the tag...maybe a typo though.
-Matt
Ok I run these 2
This is a multi-part message in MIME format.
--=_NextPart_000_00B7_01BFD9DB.5A513580
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I'm trying to do a query where I grab the date a user downloaded an eval =
version of our software from
Guys,
This gives me an error:
cfquery name="GetRev" datasource="#application.database#" dbtype="ODBC"
SELECT YEAR,
QUARTER,
REF_PERCENT,
sum(PRICE) AS tot_price,
sum(REF_RENUMERATION) AS tot_renum
FROM ORDER_AFFILIATE
WHERE AFF_ID = '#URL.RecordID#'
This gives me an error:
cfquery name="GetRev" datasource="#application.database#"
dbtype="ODBC"
SELECT YEAR,
QUARTER,
REF_PERCENT,
sum(PRICE) AS tot_price,
sum(REF_RENUMERATION) AS tot_renum
FROM ORDER_AFFILIATE
WHERE AFF_ID =
73 matches
Mail list logo
