Subject: RE: simple SQL Question
That is a left outer join. It's mixing new and old styles of joining tables.
Not sure if there's a benefit to the mix, but I reckon this is clearer:
SELECT a.id, b.name
FROM a
INNER JOIN b ON a.id = b.id
LEFT OUTER JOIN b ON a.id = b.id
Is this code actually
what does the *= mean in a query. For example:
select a.id, b.name
from a
inner join b
on a.id = b.id
where a.id *= b.id
Thanks
~|
Want to reach the ColdFusion community with something they want? Let them know
on
?
Adrian
-Original Message-
From: Discover Antartica [mailto:discoverantart...@yahoo.com]
Sent: 22 August 2009 00:50
To: cf-talk
Subject: simple SQL Question
what does the *= mean in a query. For example:
select a.id, b.name
from a
inner join b
on a.id = b.id
where a.id
*= is actually used at my work place. Thanks for the answer.
From: Adrian Lynch cont...@adrianlynch.co.uk
To: cf-talk cf-talk@houseoffusion.com
Sent: Friday, August 21, 2009 5:59:17 PM
Subject: RE: simple SQL Question
That is a left outer join. It's mixing
A simple 'group by' clause ought to get you there.
With your first example, try this:
select id, max(name), max(date_due), max(date_modified)
from yourQry
group by id
order by date_modified desc
With your second example, with the composite key, try concatenating
There's probably a really simple answer to this. Here's the sample dataset:
id namedate_duedate_modified
1 Woo 1/1/200911/18/2008 4:55PM
1 Woo 2/1/200911/18/2008 4:57PM
1 Woo 3/1/200911/18/2008 4:59PM
2 Smith 1/1/2009
select top 2 * from tables order by date asc
should get you there.
Rob
On Tue, Nov 18, 2008 at 5:00 PM, Sung Woo [EMAIL PROTECTED] wrote:
There's probably a really simple answer to this. Here's the sample
dataset:
id namedate_duedate_modified
1 Woo 1/1/2009
Hi Rob,
That would work in this instance, but I need something a little more dynamic.
What if the dataset looked like this?
id name date_due date_modified
1 Woo 1/1/2009 11/18/2008 4:55PM
1 Woo 2/1/2009 11/18/2008 5:21PM
1 Woo 3/1/2009 11/18/2008 5:30PM
2 Smith 1/1/2009
Actually, your suggestion wouldn't work for the first set, either, as it would
bring up 2 records for Smith and none for Woo.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free
Thanks, Gerald. This is close, except there is a bit of a problem. I need to
group this not by just one field but three fields, so the dataset actually
looks like this (fields 1 - 3 make up the unique key):
field 1 field 2 field 3 date_due date_modified
1 2 3 1/1/2009
What database are you using?
We're still using SQL2000, so 2005 commands won't help me here...thanks...
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
What database are you using? In MS SQL 2005 you can use the RANK and PARTITION
keywords:
SELECT id, name, date_due, date_modified FROM (
SELECT id, name, date_due, date_modified, RANK() OVER (PARTITION BY id ORDER BY
date_modified DESC) AS userRank FROM table
) AS t2
WHERE t2.userRank = 2
ORDER
SELECT id, name, date_due, date_modified
FROM SomeTable
WHERE
On Tue, Nov 18, 2008 at 3:38 PM, Sung Woo [EMAIL PROTECTED] wrote:
Actually, your suggestion wouldn't work for the first set, either, as it
would bring up 2 records for Smith and none for Woo.
I don't remember the syntax for the Sub query but something like this may
work:
SELECT TOP(date_due) as TOP_date_due, name, date_modified
FROM Table
WHERE ID IN ('SELECT DISTINCT name
FROM Table
')
GROUP BY name, date_modified, date_due
ORDER BY date_modified ,
Opps l messed up swap out name for ID in the WHERE
WHERE name IN ('SELECT DISTINCT name
SELECT TOP(date_due) as TOP_date_due, name, date_modified
FROM Table
WHERE name IN ('SELECT DISTINCT name
FROM Table
')
GROUP BY name, date_modified, date_due
ORDER BY
If you're not using SQL 2005, the following (slightly more complex) query
should simulate the RANK and PARTITION:
SELECT id, name, date_due, date_modified
FROM (
SELECT id, name, date_due, date_modified, (SELECT COUNT(id) FROM table T2 WHERE
T1.id = T2.id AND T2.date_modified =
Hi All,
I am a CF newbie and getting the hang of it but this is giving me trouble:
I have a query like this on a search action page;
cfquery name=qCar datasource=vehicles
select *
from Cars
where 0=0
cfif Trim(Form.Make) is not
and Make='#form.Make#'
/cfif
cfif FORM.Make is not
and
: Tuesday, May 29, 2001 6:14 PM
Subject: CFIF Help - Simple SQL Question:
Hi All,
I am a CF newbie and getting the hang of it but this is giving me trouble:
I have a query like this on a search action page;
cfquery name=qCar datasource=vehicles
select *
from Cars
where 0=0
cfif Trim
On 5/29/01, Denton penned:
Hi All,
I am a CF newbie and getting the hang of it but this is giving me trouble:
I have a query like this on a search action page;
snip
cfif form.startyear is not
and Year GTE #form.startyear#
/cfif
Make the last part:
and Year = #form.startyear#
CFIF
Ok I want to "conjugate" 3 fields when i am inserting into a database.
this is what i have, but it won't work, am i missing something???
cfquery datasource="data" name="add"
INSERT INTO Data(data1)
VALUES('#FORM.dat1# #FORM.dat2# #FORM.dat3#')
/cfquery
?
I believe you need to separate fields with a comma.
Hope that helps.
- Mark :o)
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 14, 2000 3:12 PM
Subject: Simple SQL Question
Ok I want to "conjugate" 3 fields when i am
urce="whatever"
INSERT INTO Data(Fieldone) Values('#alldata#)
/cfquery
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 14, 2000 6:12 PM
Subject: Simple SQL Question
Ok I want to "conjugate" 3 fields when i am i
l Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 14, 2000 3:12 PM
Subject: Simple SQL Question
Ok I want to "conjugate" 3 fields when i am inserting into a database.
this is what i have, but it won't work, am i missing something???
cfquery dataso
On 9/14/00, [EMAIL PROTECTED] penned:
Ok I want to "conjugate" 3 fields when i am inserting into a database.
this is what i have, but it won't work, am i missing something???
cfquery datasource="data" name="add"
INSERT INTO Data(data1)
VALUES('#FORM.dat1# #FORM.dat2# #FORM.dat3#')
/cfquery
Try this...
INSERT INTO TableName (FieldName1,FieldName2,FieldName3) VALUES
('#Form.Dat1#','#Form.Dat2#','#Form.Dat3#')
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 14, 2000 6:13 PM
To: [EMAIL PROTECTED]
Subject: Simple SQL Question
25 matches
Mail list logo
