what db
--
Ryan
On Fri, May 29, 2009 at 9:41 AM, Chad Gray cg...@careyweb.com wrote:
I have two tables that are joined one to many.
I would like to write a query to get the one from the first table and only
one from the many table.
TABLE1
AlbumID, AlbumTitle
1, MyPictures
TABLE2
Sorry should have mentioned that
MySQL
-Original Message-
From: Ryan Letulle [mailto:bayous...@gmail.com]
Sent: Friday, May 29, 2009 10:48 AM
To: cf-talk
Subject: Re: query question
what db
--
Ryan
On Fri, May 29, 2009 at 9:41 AM, Chad Gray cg...@careyweb.com wrote:
I have two
2 options off the top of my head:
1) have your query return as many records as there are, but limit the
output of the query by using MAXROWS attribute of cfoutput tag
2) join your first table to a sub-query that pulls only one record from
the other table in your sql:
SELECT TABLE1.*,
, 2009 10:48 AM
To: cf-talk
Subject: Re: query question
what db
--
Ryan
On Fri, May 29, 2009 at 9:41 AM, Chad Gray cg...@careyweb.com wrote:
I have two tables that are joined one to many.
I would like to write a query to get the one from the first table and
only
one from the many
mentioned that
MySQL
-Original Message-
From: Ryan Letulle [mailto:bayous...@gmail.com]
Sent: Friday, May 29, 2009 10:48 AM
To: cf-talk
Subject: Re: query question
what db
--
Ryan
On Fri, May 29, 2009 at 9:41 AM, Chad Gray cg...@careyweb.com wrote:
I have two tables
think that I need to look at
redoing this table for sure.
If you can redesign the table, the best way to handle hierarchy is using
a recursive template
like a custom tag, called with only the parentId as parameter.
The root elements in the the tree have parentId = 0.
--
I would add a column hierarchyLevel and order by that column
That won't do it: you can have element at the same level, but in
completely distinct branches in the hierarchy.
--
___
REUSE CODE! Use custom tags;
See
Bruce,
I would add a column hierarchyLevel and order by that column
~Svetlana Rudkovsky
o
o @..@
()
( )--( )
o0..0o
-Original Message-
From: Bruce Sorge [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 11, 2007 6:04 PM
To:
Actually I am going to stop re-inventing the wheel and go with an open
source CMS.
Claude Schneegans wrote:
I would add a column hierarchyLevel and order by that column
That won't do it: you can have element at the same level, but in
completely distinct branches in the hierarchy.
You can't really.
Your DB is badly designed if you have one field called hierarchy that
contains multiple elements. Its not normalised.
Normally the hierarchy would be in a different table.
But if you want to persist with this and you can change the hierarchy field
you would need to store it
[mailto:[EMAIL PROTECTED]
Sent: Thursday, October 11, 2007 5:01 PM
To: CF-Talk
Subject: RE: Query Question
You can't really.
Your DB is badly designed if you have one field called hierarchy that
contains multiple elements. Its not normalised.
Normally the hierarchy would be in a different table
Pete wrote:
Select *
From
tbl_procedures p,
procedure_asset pa
Where (
p.procedureid = pa.procedureid and
procedurecategoryid = #url.procedurecategoryid#
)
The trouble I am having is that if there is no matching record in the
procedure_asset table the
10:09 PM
To: CF-Talk
Subject: Re: Query Question
Pete wrote:
Select *
From
tbl_procedures p,
procedure_asset pa
Where (
p.procedureid = pa.procedureid and
procedurecategoryid = #url.procedurecategoryid#
)
The trouble I am having
http://mysite.verizon.net/Graeme_Birchall/id1.html
Download and go to page 302.
Dina
On 9/1/06, loathe [EMAIL PROTECTED] wrote:
I have an organization table. There is an org_id and a parent_org_id.
I am trying to get all organizations from a certain point down. The
environment is DB/2.
Have you checked to see if DB/2 supports recursive queries? Most DBs
don't, but some do. That'd be the best bet. Otherwise you're stuck
with doing the recursion manual: run that query, then run it again for
each returned row, repeating until there aren't any more rows.
If you have a common
This should help:
http://tinyurl.com/hee59
-Original Message-
From: loathe [mailto:[EMAIL PROTECTED]
Sent: Friday, September 01, 2006 3:46 PM
To: CF-Talk
Subject: Query Question
I have an organization table. There is an org_id and a parent_org_id.
I am trying to get all
Rock :)
Wish this were oracle :)
That solution for Oracle is sweet.
This should help:
http://tinyurl.com/hee59
recursion
or
something in order to go lower than that.
I'm beating myself over the head with this one, on a Friday afternoon,
any
pointers would be much appreciated.
Perhaps post an example of the SQL that doesn't work.
On 3/2/06, Will Tomlinson [EMAIL PROTECTED] wrote:
Using Access (not me, a client has to use it!)...
I have a tblStates. I need a few different tables to be related to it. A
local agency is in a state. A contributor is in a state, a state
Perhaps post an example of the SQL that doesn't work
Thanks James. I have all my stuff closed down right now, hitting the bed.
I did come up with an idea. Right now tblStates has a stateID for the PK. I
always like using ID's for PK's. If I drop stateID and let stateAbbr be the PK,
it gets
Isn't this equivalent to my query?
-Original Message-
From: Ryan Guill [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 16, 2005 5:11 PM
To: CF-Talk
Subject: Re: Query Question
You almost had it:
SELECT
eventID,
eventName,
eventDate,
FROM event a
INNER JOIN eventDates b ON a.eventID
I don't know about efficient, but try
SELECT TOP 1 eventID
That's if it's SQL Server, the over DBs will have something similar
Ade
-Original Message-
From: Russ [mailto:[EMAIL PROTECTED]
Sent: 16 November 2005 21:56
To: CF-Talk
Subject: Query Question
I have 2 tables, for the
Message-
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 16, 2005 5:03 PM
To: CF-Talk
Subject: RE: Query Question
I don't know about efficient, but try
SELECT TOP 1 eventID
That's if it's SQL Server, the over DBs will have something similar
Ade
-Original
You almost had it:
SELECT
eventID,
eventName,
eventDate,
FROM event a
INNER JOIN eventDates b ON a.eventID = b.eventID AND eventDate #now()#
actually just learned this yesterday ;)
On 11/16/05, Russ [EMAIL PROTECTED] wrote:
I have 2 tables, for the sake of simplification lets say they look
Oh yeah, if you want to limit it to one, you may want to do
max(eventDate) #now()# or min(eventDate) #now()#
On 11/16/05, Ryan Guill [EMAIL PROTECTED] wrote:
You almost had it:
SELECT
eventID,
eventName,
eventDate,
FROM event a
INNER JOIN eventDates b ON a.eventID = b.eventID AND
checked the exact syntax. if it doesn't work, I'll look it up)
- Original Message -
From: Adrian Lynch [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Wednesday, November 16, 2005 5:02 PM
Subject: RE: Query Question
I don't know about efficient, but try
SELECT TOP 1 eventID
How about this?
Select e.eventid, min(ed.eventdate) from events e inner join eventdates
ed on ed.eventid=e.eventid
Where ed.eventdate getdate()
Group by eventid
-Original Message-
From: Russ [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 16, 2005 4:56 PM
To: CF-Talk
Subject:
, Dov B (IT) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 16, 2005 5:32 PM
To: CF-Talk
Subject: RE: Query Question
How about this?
Select e.eventid, min(ed.eventdate) from events e inner join eventdates
ed on ed.eventid=e.eventid
Where ed.eventdate getdate()
Group by eventid
thinking about this more, you could probably do it even easier like so:
SELECT
eventID,
eventName,
MIN(eventDate)
FROM event a
INNER JOIN eventDates b ON a.eventID = b.eventID
WHERE eventDate #now()#
GROUP BY eventID, eventName
that should bring back one record for each id. The only catch on
Yup. Look at left and right outer joins.
http://www.w3schools.com/sql/sql_join.asp
What DB are you using?
Ade
-Original Message-
From: Mark Fuqua [mailto:[EMAIL PROTECTED]
Sent: 29 September 2005 13:45
To: CF-Talk
Subject: query question
Morning,
When I use joins with a select
This database is MS Access. I don't know if it supports right joins. Is
the structure of the join the same, just exchange right for left?
Mark
-Original Message-
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 29, 2005 8:55 AM
To: CF-Talk
Subject: RE: query
Use an OUTER JOIN.
SELECT *
FROM table01 one
INNER JOIN table02 two
ON one.id = two.fk_id
This will only return rows from table01 that also have matching values in
table02.
SELECT *
FROM table01 one
LEFT JOIN table02 two
ON one.id = two.fk_id
This, however, will return ALL rows
[mailto:[EMAIL PROTECTED]
Sent: Thursday, September 29, 2005 8:16 AM
To: CF-Talk
Subject: RE: query question
This database is MS Access. I don't know if it supports right joins. Is
the structure of the join the same, just exchange right for left?
Mark
-Original Message-
From: Adrian Lynch
: RE: query question
This database is MS Access. I don't know if it supports right joins. Is
the structure of the join the same, just exchange right for left?
Mark
-Original Message-
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 29, 2005 8:55 AM
To: CF-Talk
@houseoffusion.com
Sent: Thursday, September 29, 2005 1:55 PM
Subject: RE: query question
Yup. Look at left and right outer joins.
http://www.w3schools.com/sql/sql_join.asp
What DB are you using?
Ade
-Original Message-
From: Mark Fuqua [mailto:[EMAIL PROTECTED]
Sent: 29 September 2005 13:45
Do I need to do subselects with date ranges in each or will that not help
either.if anyone can quickly help that would be great!! Thanks!
From: Eric J. Hoffman [mailto:[EMAIL PROTECTED]
Sent: Wed 5/25/2005 4:49 PM
To: CF-Talk
Subject: Query question
I am
: Re: query question
On May 6, 2004, at 9:25 PM, Tony Weeg wrote:
am using sql server, I have aqua data studio, im using query analyzer
right
now...
I don't have any other way of getting to the count, that's the
problem there
isnt anything else in the vehicle table that links back
]
410.548.2337
www.navtrak.net
-Original Message-
From: Dick Applebaum [mailto:[EMAIL PROTECTED]
Sent: Friday, May 07, 2004 12:46 AM
To: CF-Talk
Subject: Re: query question
On May 6, 2004, at 9:25 PM, Tony Weeg wrote:
am using sql server, I have aqua data studio, im using query
analyzer
Tony
why don't you modify the queries to return the actual data columns in
the joins in addition to the count -- that way you can see what the db
is returning
HTH
Dick
On May 6, 2004, at 8:13 PM, Tony Weeg wrote:
ok, in the two queries below, the salesOrderDetailId and the productId
are
...this isnt making
sense
hmmm
tony
-Original Message-
From: Dick Applebaum [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 11:52 PM
To: CF-Talk
Subject: Re: query question
Tony
why don't you modify the queries to return the actual data columns in the
joins in addition
What usually helps me in a case like this is to simplify the query (if
you can).
1) Take out all the joins
2) add them back one at a time
Kinda' build the complex query in a series of simple steps..
Do you have a interactive SQL client that you can use to do this?
It looks like you are using
-Original Message-
From: Dick Applebaum [mailto:[EMAIL PROTECTED]
Sent: Friday, May 07, 2004 12:22 AM
To: CF-Talk
Subject: Re: query question
What usually helps me in a case like this is to simplify the query (if you
can).
1) Take out all the joins
2) add them back one at a time
Kinda' build
[mailto:[EMAIL PROTECTED]
Sent: Friday, May 07, 2004 12:22 AM
To: CF-Talk
Subject: Re: query question
What usually helps me in a case like this is to simplify the query
(if you
can).
1) Take out all the joins
2) add them back one at a time
Kinda' build the complex query in a series of simple
Thanks for the responses. I know which query it is so fixing it is in the
works, one of my colleagues is also looking into it. I still don't think it
makes sense that the query going to a database on a different server woud
cause CF to spike, I'd think it would cause the database server's cpu
It appears a certain query of mine is causing our CPU to spike. We're
working on the query but what is strange is that it will hose
CF. It was my
uderstanding that CF passes the connection off so I would
think it would be
normal for the database server's CPU to spike but not for CF
to cause
I had a similar problem too - we restructured the queries to use INNER JOINS
and it solved it. It went from CPU flat out to barely a blip on the radar
screen
**
Kevin Parker
Web Services Manager
WorkCover Corporation
e: [EMAIL PROTECTED]
w: www.workcover.com
p:+ 61 8 8233 2548
f: +61 8
We have watched the web-server spike up to 100 percent utilization on
cumbersome queries before (afterward the page was modified to reduce the
number of results returned). Im not sure if this is supposed to happen but
it did.
Casey Cook
Remove the %'s
-Original Message-
From: Double Down, Inc. [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 11 December 2002 10:05 a.m.
To: CF-Talk
Subject: Query Question
I have this statement in a query:
(cfloop index=ii list=#form.seeking#
delimiters=,seeking LIKE '%#ii#%'cfif
make sure when you put values in the seeking column to add a comma before
and after the list. So a list of 1,2,3 becomes ,1,2,3,
That way when you run the query you can say:
WHERE seeking LIKE '%,2,%'
+---+
Bryan Love
Macromedia Certified
Jillian Carroll wrote:
I have this query (that doesn't work):
What is the rror?
SELECT pa_ab, pa_sk, pa_mb, pa_on, pa_nb, pa_pei, pa_ns, pa_nf, pa_bc,
pa_bc, pa_pq, pa_yk, pa_nwt, pa_nu
FROM USERS
WHERE pa_ab=1 OR pa_sk=1 OR pa_mb=1 OR pa_on=1 OR pa_nb=1 OR pa_pei=1
OR pa_ns=1 OR pa_nf=1
Your query looks fine. What do you mean it doesn't work? Are you getting
an error message or just not getting the results you expected?
--
Mosh Teitelbaum
evoch, LLC
Tel: (301) 625-9191
Fax: (301) 933-3651
Email: [EMAIL PROTECTED]
WWW: http://www.evoch.com/
-Original Message-
From:
If these are in fact logical values, ie 0 or 1, then you could try
something like:
SELECT pa_ab, pa_sk, pa_mb, pa_on, pa_nb, pa_pei, pa_ns, pa_nf, pa_bc,
pa_bc, pa_pq, pa_yk, pa_nwt, pa_nu
FROM USERS
WHERE (pa_ab + pa_sk + pa_mb + pa_on + pa_nb + pa_pei + pa_ns + pa_nf +
pa_bc + pa_bc + pa_pq +
You need to use a union join.
Adam.
-Original Message-
From: Trace [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 2:36 PM
To: CF-Talk
Subject: Query question
Is there a way to refer to 2 fields in one query with the same name?
I have a field called title in table
What are you trying to get?
1. a query with all the titles from pages added to all the names from categories?
2. a single field that concatenates the title and the name together?
3. something I'm not seeing?
Jerry Johnson
[EMAIL PROTECTED] 05/28/02 02:53PM
Is there a way to refer to 2
Is there a way to refer to 2 fields in one query with
the same name?
I have a field called title in table tbl_Pages and a
field called Name in table tbl_Categories
cfquery name=getHyperlinks datasource=#ds#
SELECTtbl_Pages.title AS hyperlink_title,
Trace,
What exactly is your intent? It seems that you want to create a list of all
the hyperlink_title values, whether they appear in tbl_Pages or
tbl_Categories. Is that right?
In other words, if you have tbl_Pages:
tbl_Pages_pk hyperlink_title
1 A
2 B
3
It was for concatenating the 2 fields into one. But Dave answered my
question.
Thank you,
Trace
- Original Message -
From: Jerry Johnson
To: CF-Talk
Sent: Tuesday, May 28, 2002 1:04 PM
Subject: Re: Query question
What are you trying to get?
1. a query with all the titles from pages
You can also do
SELECT 'hyperlink_title'=tbl_Pages.title+' '+tbl_Categories.Name from
tbl_Pages,tbl_Categories
Joe
- Original Message -
From: Dave Watts [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Tuesday, May 28, 2002 4:02 PM
Subject: RE: Query question
Is there a way
model, not in a flat model.
christopher olive, cto, vp of web development
atnet solutions, inc.
410.931.4092
http://www.atnetsolutions.com
-Original Message-
From: Matthew R. Small [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 07, 2002 6:41 PM
To: CF-Talk
Subject: RE: Query question
-Original Message-
From: Christopher Olive [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 08, 2002 10:45 AM
To: CF-Talk
Subject: RE: Query question
it's becaus Access is close enought to a RDBMS to not have the concept
of ordering an unordered recordset. when an RDBMS query is run
]]
Sent: Friday, February 08, 2002 3:51 PM
To: CF-Talk
Subject: RE: Query question
Chris,
In MSSQL server you can create a clustered index that is the actual
storage order for the table. Also, in SQL 2000 you can specify the
custered
order as ascending or descending. This can be very useful if you
Has anybody used Doubleclicks DARTmail Connect XML interface with CF?
Thanks peeps!
Brook
__
Why Share?
Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free
Ah, but when you delete records Access uses the deleted
space again AFAIK. So, the records that are physically
in the DB may not be in the order that you think...
This, unfortunately, isn't the case. Access doesn't reuse deleted space,
which is why you really don't want to use Access to
How do you know what the order is in the database? That is usually a hidden thing
that you wouldn't see. At any rate, it's best
prctice to use ORDER BY.
Regards,
--
Howie Hamlin - inFusion Project Manager
On-Line Data Solutions, Inc. - www.CoolFusion.com - 631-737-4668 x101
inFusion Mail
Small
-Original Message-
From: Howie Hamlin [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 07, 2002 6:29 PM
To: CF-Talk
Subject: Re: Query question
How do you know what the order is in the database? That is usually a
hidden thing that you wouldn't see. At any rate, it's best
prctice
: RE: Query question
It's in that order in the access database that I use - using autonumber.
And you're right, it is best to use order by. However, I was wondering
what would have happened if I needed information in my database in the
order that I view it, but it has no logical ordering column
Howie Hamlin wrote:
Ah, but when you delete records Access uses the deleted space again AFAIK.
Don't think so. That is what the Compact Repair option is for.
Jochem
__
Get Your Own Dedicated Windows 2000 Server
PIII 800 /
Yup.
--
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
--
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387
sure. you can make a DSN on machine 1 for a database on machine 2.
-Original Message-
From: ibtoad [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 8:31 AM
To: CF-Talk
Subject: Query Question
Is it possible to query a database on a different server? What I mean is
can I have
Is it possible to query a database on a different server? What I mean is
can I have a template located at http://www.site1.com query a database
located at http://www.site2.com assuming I have the propper user id and
password?
ODBC sources aren't dealt with via HTTP, therefore anything
How??
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 11:34 AM
To: CF-Talk
Subject: RE: Query Question
Yup.
--
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http
Can you write me a sample query?
Rich
-Original Message-
From: Dylan Bromby [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 11:32 AM
To: CF-Talk
Subject: RE: Query Question
sure. you can make a DSN on machine 1 for a database on machine 2.
-Original Message-
From
8890.
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: 19 February 2001 16:34
To: CF-Talk
Subject: RE: Query Question
Yup.
--
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http
wrote:
Can you write me a sample query?
Rich
-Original Message-
From: Dylan Bromby [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 11:32 AM
To: CF-Talk
Subject: RE: Query Question
sure. you can make a DSN on machine 1 for a database on machine 2.
-Original Message
by the DSN. just use the
datasource name you give it when you write your query.
if i'm missing something that makes this more complex, let me know...
-Original Message-
From: ibtoad [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 8:49 AM
To: CF-Talk
Subject: RE: Query Question
Can
, or
retain this message. Please notify us on +44 (0)207 387 8890.
-Original Message-
From: Dylan Bromby [mailto:[EMAIL PROTECTED]]
Sent: 19 February 2001 17:52
To: CF-Talk
Subject: RE: Query Question
do you know how to set up a DSN? the only thing you'll do differently, is in
the CF admin
that looks good to me.
~Simon
Simon Horwith
Certified ColdFusion Developer
Fig Leaf Software
1400 16th St NW, # 220
Washington DC 20036
202.797.6570 (direct line)
www.figleaf.com
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 29, 2000
On 11/29/00, Chad Gray penned:
I would like to get all the data with a value of 1234 in the field
JobNum. Once i collect these records i would like to get the Record with
the highest ACTLOGID value.
Can you do a :
SELECT max(ACTLOGID), JobNum
FROM activitylog
Where Jobnum='1234'
Absolutely,
looks good to me too, but you might want to use an alias for the max field.
SELECT max(ACTLOGID) as maxLogIn, JobNum
-- Bruce
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 29, 2000 2:50 PM
To: CF-Talk
Subject: Query Question
I
AH the AS part was holding me up!
Thanks! I didnt see anything about AS in my SQL books...
Maybe someone needs to write a book about databases command structure for
CF.. :)
At 05:37 PM 11/29/00 -0500, you wrote:
On 11/29/00, Chad Gray penned:
I would like to get all the data with a
Try this:
CFLOOP QUERY="GetValidId"
cfif isdefined("#evaluate('form.'id)#")
You don't need to use Evaluate within IsDefined - it automatically evaluates
its contents:
cfif IsDefined("Form." id)
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202)
on 11/21/00 3:14 PM, Mark Connor at [EMAIL PROTECTED] wrote:
CFLOOP QUERY="GetValidId"
!--- this isdefined does not work ---
cfif isdefined("#form.#id##")
cfset idlist = idlist '#id#,'
/cfif
/CFLOOP
How do I write the isdefined to make it work? (every thing I've tried
throws an
I am trying to loop through valid ID's (some ID's are "unlocked" and some
are "locked" the user could only choose unlocked id's.) I than am
comparing
them them to see which ones were checked. And appending them to a list to
be
used later:
CFLOOP QUERY="GetValidId"
!--- this isdefined does
Try this:
CFLOOP QUERY="GetValidId"
cfif isdefined("#evaluate('form.'id)#")
Um, no. Essentially what you've told this to do is to see if there is a
variable with the name of the value of the evaluated variable that may or
may not exist. The above code may work in some bizzare situations,
on 11/21/00 7:44 PM, Justin Scott at [EMAIL PROTECTED] wrote:
Um, no. Essentially what you've told this to do is to see if there is a
variable with the name of the value of the evaluated variable that may or
may not exist. The above code may work in some bizzare situations, but not
as a
I'd replace your where clauses with JOIN stetements to ensure you get the
right type of join.
The types of joins you can use are:
INNER JOIN (pretty much the same as a where)
LEFT OUTER JOIN - Include all rows from the left hand table but only those
that match from the right hand one.
RIGHT
Thank you Andy for the information. I'll check these out.
Jim Ray
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 11, 2000 7:58 AM
To: CF-Talk
Subject: RE: Query Question
I'd replace your where clauses with JOIN stetements to ensure you get
Robert:
Why don't you do a query for you select box that selects only the values
that are in the first table but not in the second. Kind of like so:
SELECT KeyID_Table1 from Table1
WHERE KeyID_Table1 NOT IN (select KeyID_Table2 from Table2)
My syntax may be off, but I think the idea is
Is it required to have two tables, one for all choices and one for selected
values? If you could use a single table with an additional 'selected' flag
field the query would be simple. However, if that is not a possibility you
can try something like:
SELECT [field] FROM tblALL WHERE [field]
If I understand you correctly, both the selected and unselected values are
in their own tables so you could just do something like this, right?
select foo
from unselected_table
where foo not in (select foo from selected_table)
HTH
Jon
- Original Message -
From: netman [EMAIL
Put the loop outside the query tag.
cfloop list="#SI_Item_ID#" index="TheItem"
cfquery name="qgetitems" datasource="#DSN#"
SELECT *
FROM SI_ITEMS2
WHERE SI_ITEM_ID = #TheITem#
AND SI_Opsrpt_ID = #qgetsi.SI_Opsrpt_ID#
Kelly,
You can't execute 3 SELECT statements in a single query that way. You should
combine them into one statement that might look something like this:
cfquery name="qgetitems" datasource="#DSN#"
SELECT *
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN
-Original Message-
From: Hinojosa, Robert [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 10, 2000 1:36 PM
To: '[EMAIL PROTECTED]'
Subject: RE: QUERY QUESTION
Put the loop outside the query tag.
cfloop list="#SI_Item_ID#" index="TheItem"
cfquery name=&q
//www.airportnet.org
---
-Original Message-
From: Ben Forta [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, August 10, 2000 1:45 PM
To: [EMAIL PROTECTED]
Subject: RE: QUERY QUESTION
Robert, actually, that would not work. As the same query name is used each
Kelly,
All I can do is guess since I don't know your db design but here goes:
-Run your first query
-Run this query
cfquery name="qgetitems" datasource="#DSN#"
SELECT COUNT(SI_Item_ID) AS SI_Item_ID_COUNT, SI_Item_ID
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN (#SI_Item_ID#)
AND SI_Opsrpt_ID =
select company, item
fromcompanies, items
where companies.company = items.company
and companies.private = 'NO'
and items.item = 600
i think..
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 31, 2000 11:06 PM
To: [EMAIL
95 matches
Mail list logo
