Jim Wright wrote:
Just for clarification, is the outer join syntax that MS-SQL 2005
deprecates (WHERE foo *= blah) non-standard...if so, was it ever
standard?
It isn't standard and I don't think it has ever been.
Is there ANSI compliant syntax for doing outer
joins other than using
Jim... nice work.. I am not overly familiar with using the Select in
the order by column but it works great in MS-SQL as you say (MS Access
doesn't seem to like it ! )
On 9/20/06, Jim Wright [EMAIL PROTECTED] wrote:
Matt Williams wrote:
Rich, you are right in that I want the event with the
Several people wrote:
ROM table1,table2 WHERE table1.foo = table2.foo (Is NOT ANSI compliant
SQL)
There is nothing wrong with that syntax under any version of the SQL standard
from ANSI or the ISO/IEC. This syntax is not even deprecated in the standard
and I think it is highly unlikelly it
Jochem van Dieten wrote:
Several people wrote:
ROM table1,table2 WHERE table1.foo = table2.foo (Is NOT ANSI compliant
SQL)
There is nothing wrong with that syntax under any version of the SQL standard
from ANSI or the ISO/IEC. This syntax is not even deprecated in the standard
and I
expressed within this
communication are not necessarily those expressed by Reed Exhibitions.
Visit our website at http://www.reedexpo.com
-Original Message-
From: Andrew Scott
To: CF-Talk
Sent: Wed Sep 20 06:47:44 2006
Subject: RE: How to get these query results
Michael,
I was under
)
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, 20 September 2006 3:59 PM
To: CF-Talk
Subject: Re: How to get these query results
You would not need any kind on outer join left or otherwise if the
relationship is inner.
I agree that the comma seperated inner join syntax is newb but the main
reason you
Got it, and I understand you're your point, so thanks.
-Original Message-
From: Andrew Scott [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 19, 2006 10:48 PM
To: CF-Talk
Subject: RE: How to get these query results
Michael,
I was under the impression you know what inner
://www.reedexpo.com
-Original Message-
From: Andrew Scott
To: CF-Talk
Sent: Wed Sep 20 07:06:51 2006
Subject: RE: How to get these query results
Neil,
Actually looking at the data output required I would say a right outer join
is required here and then the order by will work as required
Sorry Neil,
I thre that in for the orignal poster to read, not making it aimed at you.
On 9/20/06, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED]
wrote:
I didn't even look at the problem as everyone had already noted an inner
join hence why I noted if the relationship is inner.
Lol, no worries, I was viewing on my Blackberry and didn't scroll down too
far..
-Original Message-
From: Andrew Scott [mailto:[EMAIL PROTECTED]
Sent: 20 September 2006 10:49
To: CF-Talk
Subject: Re: How to get these query results
Sorry Neil,
I thre that in for the orignal poster
Why is it bad code? Is it slower to process?
Rick
-Original Message-
From: Andrew Scott [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 20, 2006 1:03 AM
To: CF-Talk
Subject: RE: How to get these query results
Michael,
You really need to learn inner joins, sorry but that is bad
As noted, it is non-standard.
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: 20 September 2006 12:32
To: CF-Talk
Subject: RE: How to get these query results
Why is it bad code? Is it slower to process?
Rick
-Original Message-
From: Andrew Scott
PROTECTED]
Sent: Wednesday, September 20, 2006 7:39 AM
To: CF-Talk
Subject: RE: How to get these query results
As noted, it is non-standard.
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: 20 September 2006 12:32
To: CF-Talk
Subject: RE: How to get these query
the (experts)
Regards,
Doug
- Original Message -
From: Robertson-Ravo, Neil (RX) [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Wednesday, September 20, 2006 5:39 AM
Subject: RE: How to get these query results
As noted, it is non-standard.
-Original Message
this from all
the (experts)
Regards,
Doug
- Original Message -
From: Robertson-Ravo, Neil (RX) [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Wednesday, September 20, 2006 5:39 AM
Subject: RE: How to get these query results
As noted, it is non-standard
:54
To: CF-Talk
Subject: RE: How to get these query results
Hi, Neil...I think out conversations are crossing...(reminds me of
why I hated chat rooms... :oP)
Anyway, the code I was referring to was in the original post, not
any post referencing joins...
cfquery name=your_query
SELECT *
FROM
-Talk
Subject: RE: How to get these query results
Yeah could be crossing, but it is this syntact which is non-standard and
should be avoided.
SELECT * FROM dates_table, events_table
Where the INNER JOIN syntax is substituted with a comma.
-Original Message-
From: Rick Faircloth
Rick Faircloth wrote:
You're saying that the comma in the select statement
is non-standard
and that the Inner Join syntax as a substitute for the comma
is more appropriate?
Appropriate, maybe...standard, yes.
FROM table1,table2 WHERE table1.foo = table2.foo (Is NOT ANSI compliant SQL)
You need to order by both, first the item on which you want to group,
then the item that you want sorted within that group. I've used a left
outer join just incase you have events that do not have dates entered.
Hopefully, I've not flubbed this up, and this works for you:
cfquery name=myevents
Sort on both
SELECT a.EventName,b.EventDates
FROM Events a INNER JOIN EventsDate b ON a.EventID = b.EventID
ORDER BY a.EventName,b.EventDates
Then cfoutout grouping on EventName.
Matt Williams wrote:
I'm gonna try this again as while I slept last night my previous post
turned into a
Indeed..
ROM table1,table2 WHERE table1.foo = table2.foo (Is NOT ANSI compliant
SQL)
This should be avoided.
-Original Message-
From: Jim Wright [mailto:[EMAIL PROTECTED]
Sent: 20 September 2006 13:54
To: CF-Talk
Subject: Re: How to get these query results
Rick Faircloth wrote
I'll let the experts enjoin the fight about
the coding...but, I'm curious as to
what type of application your coding for...a calendar and recurring events,
or
something else?
I always put my event dates in the same table as the event ID,
event name, etcI was just wondering if I'm missing
Matt
You can try the following. It seems to work... my gut feeling tells me
there may be an easier way with less queries but I can't think of
anything right now. You may want to consider caching queries for speed
depending on how much the data is changing.
!--- get all event details ---
cfquery
I presume the design is based on one table for the event names and a
separate for instances of those events on a given date which seems
sensible.
Matt if you need the output based on the event names alphabetically
and then the dates desc in them then Jim/Kris code is great...
my assumption was
Rich, you are right in that I want the event with the furthest/newest
date to show first. But if there is another date associated with that
newest event, it should also show under its title. Your solution looks
like it will work. I'll give it a try and let you know.
I am hoping to find a bit more
The test page works OK with a www. added:
http://www.fclounge.cfdeveloper.co.uk/test.cfm
-Original Message-
From: Matt Williams [mailto:[EMAIL PROTECTED]
Sent: 20 September 2006 14:59
To: CF-Talk
Subject: Re: How to get these query results - Take 2
Rich, you are right in that I
Matt Williams wrote:
Rich, you are right in that I want the event with the furthest/newest
date to show first. But if there is another date associated with that
newest event, it should also show under its title. Your solution looks
like it will work. I'll give it a try and let you know.
I
On 9/20/06, Kris Jones [EMAIL PROTECTED] wrote:
You need to order by both, first the item on which you want to group,
then the item that you want sorted within that group. I've used a left
outer join just incase you have events that do not have dates entered.
Hopefully, I've not flubbed this
Thanks for the answer...that's just the explanation I was looking for...
Rick
-Original Message-
From: Jim Wright [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 20, 2006 8:54 AM
To: CF-Talk
Subject: Re: How to get these query results
Rick Faircloth wrote:
You're saying
: Matt Williams [mailto:[EMAIL PROTECTED]
Sent: 20 September 2006 14:59
To: CF-Talk
Subject: Re: How to get these query results - Take 2
Rich, you are right in that I want the event with the furthest/newest
date to show first. But if there is another date associated with that
newest event
On 9/20/06, Jim Wright [EMAIL PROTECTED] wrote:
This could be done with a subquery in the ORDER BY...
SELECT a.EventName,b.EventDates
FROM Events a INNER JOIN EventsDate b ON a.EventID = b.EventID
ORDER BY (SELECT max(eventdates) FROM EventsDate WHERE eventid =
a.eventid)
On 9/20/06, Rick Faircloth [EMAIL PROTECTED] wrote:
I'll let the experts enjoin the fight about
the coding...but, I'm curious as to
what type of application your coding for...a calendar and recurring events,
or
something else?
Rick, the actual data has nothing to do with events. I changed
But why would you separate the dates from the documents?
Are you keeping a version archive or update history?
Rick
-Original Message-
From: Matt Williams [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 20, 2006 10:56 AM
To: CF-Talk
Subject: Re: How to get these query results
On 9/20/06, Rick Faircloth [EMAIL PROTECTED] wrote:
But why would you separate the dates from the documents?
Are you keeping a version archive or update history?
Yes, it's an update history. One document has many updates. It's
actually comments about the document, not changes to the document
The easiest way I've seen to do this is to have a most recent update date
field in the events or documents table. An insert/update trigger on the
updates table can guarantee that the most recent update field will always
be accurate. Then, all you need is a query something like this:
select
On 9/20/06, Rick Faircloth [EMAIL PROTECTED] wrote:
cfquery name=your_query
SELECT *
FROMdates_table,
events_table
WHERE dates_table.EventID = events_table.EventID
/cfquery
But, my question concerning it being bad code was, really, how
much slower
Select * from Table1
Inner join Table2 on table1.EventId = table2.eventId
You might need a left or right outer join though
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613 8676 4223
Mobile: 0404 998 273
-Original Message-
From: Matt Williams
Oops
Sorry then you can
cfoutput query=QueryName group=EventId
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613 8676 4223
Mobile: 0404 998 273
~|
Introducing the Fusion Authority Quarterly
cfquery name=your_query
SELECT *
FROMdates_table,
events_table
WHERE dates_table.EventID = events_table.EventID
/cfquery
cfoutput query=your_query group=EventID
#EventName#br
cfoutput group=EventDate
#EventDate#br
/cfoutput
br
, 20 September 2006 2:55 PM
To: CF-Talk
Subject: RE: How to get these query results
cfquery name=your_query
SELECT *
FROMdates_table,
events_table
WHERE dates_table.EventID = events_table.EventID
/cfquery
cfoutput query=your_query group=EventID
#EventName#br
: Tuesday, September 19, 2006 10:03 PM
To: CF-Talk
Subject: RE: How to get these query results
Michael,
You really need to learn inner joins, sorry but that is bad sql code
without
it.
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613 8676 4223
Mobile: 0404 998
reading this thread.
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613 8676 4223
Mobile: 0404 998 273
-Original Message-
From: Michael E. Carluen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 20 September 2006 3:14 PM
To: CF-Talk
Subject: RE: How to get these query
will prevent any unexpected results as you
stated.
-Original Message-
From: Andrew Scott [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 19, 2006 10:21 PM
To: CF-Talk
Subject: RE: How to get these query results
That's ok, but for anyone else reading this. Although the sql is ok
: Michael E. Carluen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 19, 2006 10:33 PM
To: CF-Talk
Subject: RE: How to get these query results
And in this case a left outer join might be better, because the results
might not be what you would normally expect.
Andrew, pls explain what you
to get these query results
And in this case a left outer join might be better, because the results
might not be what you would normally expect.
Andrew, pls explain what you meant by might not be what you normally
expect? by a simple SELECT * query with a simple cfoutput grouping. And how
using
: Michael E. Carluen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 20 September 2006 3:39 PM
To: CF-Talk
Subject: RE: How to get these query results
Btw, Andrew, can you also send your idea on how it should be done on the SQL
query with joins, but this time include your CF tags to output the results?
I
AMEN!
-Original Message-
From: Andrew Scott [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 20, 2006 12:48 AM
To: CF-Talk
Subject: RE: How to get these query results
Michael,
I was under the impression you know what inner and outer joins are?
But lets take your SQL Code
47 matches
Mail list logo
