Re: (ot) SQL Question - flattening data

[Dominic Watson]

Depending on what you are doing with this data, seems to me that this should
be done in the front end and not the db. So get your data in the original
format:

USER, CODE
rick,AL
rick,FR
rick,TR
rick,HS
joe,AL
joe,FU
Bob,FM

And then use cfoutput with query and group (a very rough output here):

table
 thead
   trthUser/ththCodes/th
 /thead
 tbody

cfoutput query=theQuery group=user
 tr
  td#user/td
  td
cfoutput#code# /cfoutput
  /td
 tr
/cfoutput


 /tbody
/table

Dominic

2009/11/13 Rick Root rick.r...@gmail.com


 From the documentation, pivot tables seem to require aggregate
 functions... The generic description would seem to work but the
 examples make it difficult to see how.

 But... I figured out a solution!  Using SQL Server's row_number() over
 (partition by XXX order by XXX) I can make a subquery that returns
 data like this

 entityid,rownum,rectyp

 And then run this query (tb901 is my primary table)

 select
tb901.entityid,
R1.rectypcd as rectype1,
r2.rectypcd as rectype2,
r3.rectypcd as rectype3,
r4.rectypcd as rectype4,
r5.rectypcd as rectype5
 from
tb901
left join
(
select row_number() over(partition by entityid order
 by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R1 on tb901.entityid=R1.entityid and R1.rownum=1
left join
(
select row_number() over(partition by entityid order
 by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R2 on tb901.entityid=R2.entityid and R2.rownum=2
left join
(
select row_number() over(partition by entityid order
 by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R3 on tb901.entityid=R3.entityid and R3.rownum=3
left join
(
select row_number() over(partition by entityid order
 by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R4 on tb901.entityid=R4.entityid and R4.rownum=4
left join
(
select row_number() over(partition by entityid order
 by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R5 on tb901.entityid=R5.entityid and R5.rownum=5

 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328396
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: (ot) SQL Question - flattening data

[Dominic Watson]

Just read the original post properly - please ignore me.

Dominic

2009/11/15 Dominic Watson watson.domi...@googlemail.com

 Depending on what you are doing with this data, seems to me that this
 should be done in the front end and not the db. So get your data in the
 original format:

 USER, CODE

 rick,AL
 rick,FR
 rick,TR
 rick,HS
 joe,AL
 joe,FU
 Bob,FM

 And then use cfoutput with query and group (a very rough output here):

 table
  thead
trthUser/ththCodes/th
  /thead
  tbody

 cfoutput query=theQuery group=user
  tr
   td#user/td
   td
 cfoutput#code# /cfoutput
   /td
  tr
 /cfoutput


  /tbody
 /table

 Dominic

 2009/11/13 Rick Root rick.r...@gmail.com


 From the documentation, pivot tables seem to require aggregate
 functions... The generic description would seem to work but the
 examples make it difficult to see how.

 But... I figured out a solution!  Using SQL Server's row_number() over
 (partition by XXX order by XXX) I can make a subquery that returns
 data like this

 entityid,rownum,rectyp

 And then run this query (tb901 is my primary table)

 select
tb901.entityid,
R1.rectypcd as rectype1,
r2.rectypcd as rectype2,
r3.rectypcd as rectype3,
r4.rectypcd as rectype4,
r5.rectypcd as rectype5
 from
tb901
left join
(
select row_number() over(partition by entityid
 order by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R1 on tb901.entityid=R1.entityid and R1.rownum=1
left join
(
select row_number() over(partition by entityid
 order by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R2 on tb901.entityid=R2.entityid and R2.rownum=2
left join
(
select row_number() over(partition by entityid
 order by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R3 on tb901.entityid=R3.entityid and R3.rownum=3
left join
(
select row_number() over(partition by entityid
 order by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R4 on tb901.entityid=R4.entityid and R4.rownum=4
left join
(
select row_number() over(partition by entityid
 order by rectypcd)
 as rownum, entityid, rectypcd
from tb906
) R5 on tb901.entityid=R5.entityid and R5.rownum=5

 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328397
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: (ot) SQL Question - flattening data

[Dave Phelan]

You wrote a pivot query without using pivot.  BTW, the aggregate for the pivot 
query can be Count().

-Original Message-
From: Rick Root [mailto:rick.r...@gmail.com] 
Sent: Friday, November 13, 2009 4:19 PM
To: cf-talk
Subject: Re: (ot) SQL Question - flattening data


From the documentation, pivot tables seem to require aggregate
functions... The generic description would seem to work but the
examples make it difficult to see how.

But... I figured out a solution!  Using SQL Server's row_number() over
(partition by XXX order by XXX) I can make a subquery that returns
data like this

entityid,rownum,rectyp

And then run this query (tb901 is my primary table)

select
tb901.entityid,
R1.rectypcd as rectype1,
r2.rectypcd as rectype2,
r3.rectypcd as rectype3,
r4.rectypcd as rectype4,
r5.rectypcd as rectype5
from
tb901
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R1 on tb901.entityid=R1.entityid and R1.rownum=1
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R2 on tb901.entityid=R2.entityid and R2.rownum=2
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R3 on tb901.entityid=R3.entityid and R3.rownum=3
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R4 on tb901.entityid=R4.entityid and R4.rownum=4
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R5 on tb901.entityid=R5.entityid and R5.rownum=5



~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328395
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: (ot) SQL Question - flattening data

[Dave Phelan]

Is there a particular reason to return them in this format?  I would think that 
the straight query output would be simpler to work with. However, you can 
accomplish this either by using cursors to loop over the query output and build 
what you are looking for or by building a crosstab query of the data.  I 
haven't built a crosstab query in quite a while and don't remember all the 
specifics, but the output would be similar to:


Entity  AL  FR  TR  HS  FU  FM
RickX   X   X   X
Joe X   X
Bob X


Crosstab queries can be a little hairy to build.  IMHO, go with the cursors.



-Original Message-
From: Rick Root [mailto:rick.r...@gmail.com] 
Sent: Friday, November 13, 2009 10:41 AM
To: cf-talk
Subject: (ot) SQL Question - flattening data


I'm trying to flatten out some data using only SQL we currently
have a mainframe job that produces a datafeed for me uses cobol to
do the work of looping through all the entities and putting up to 5
record types in 5 record type fields in the output file.  I'm trying
to figure out a way to do it with SQL alone so I can just use a
transact-sql job to produced my flattened reporting table.

So for example, let's say I've got a table like this:

create table entityRecordTypes
(
entityid char(10),
recordType char(2),
primary key (entityid, recordType)
);

How do I get from here ...

rick,AL
rick,FR
rick,TR
rick,HS
joe,AL
joe,FU
Bob,FM

to a view or table that has this structure 

entityid,rectype1,rectype2,rectype3,rectype4,rectype5
rick,AL,FR,TR,HS,NULL
joe,AL,FU,NULL,NULL,NULL
bob,FM,NULL,NULL,NULL,NULL

using SQL.

if an entity had more than 5 record types, only the first 5 would be
put into the output table/view.

Rick



~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328354
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: (ot) SQL Question - flattening data

[DURETTE, STEVEN J (ATTASIAIT)]

Actually, if MSSQL 2005+ is being used, PIVOT might come in handy here.

I had a procedure that used cursors. It ran for 2 minutes.  I converted
it to use PIVOT instead and I get the same results in 2 seconds!

Steve


-Original Message-
From: Dave Phelan [mailto:dphe...@lifepoint.com] 
Sent: Friday, November 13, 2009 1:11 PM
To: cf-talk
Subject: RE: (ot) SQL Question - flattening data


Is there a particular reason to return them in this format?  I would
think that the straight query output would be simpler to work with.
However, you can accomplish this either by using cursors to loop over
the query output and build what you are looking for or by building a
crosstab query of the data.  I haven't built a crosstab query in quite a
while and don't remember all the specifics, but the output would be
similar to:


Entity  AL  FR  TR  HS  FU  FM
RickX   X   X   X
Joe X   X
Bob X


Crosstab queries can be a little hairy to build.  IMHO, go with the
cursors.



-Original Message-
From: Rick Root [mailto:rick.r...@gmail.com] 
Sent: Friday, November 13, 2009 10:41 AM
To: cf-talk
Subject: (ot) SQL Question - flattening data


I'm trying to flatten out some data using only SQL we currently
have a mainframe job that produces a datafeed for me uses cobol to
do the work of looping through all the entities and putting up to 5
record types in 5 record type fields in the output file.  I'm trying
to figure out a way to do it with SQL alone so I can just use a
transact-sql job to produced my flattened reporting table.

So for example, let's say I've got a table like this:

create table entityRecordTypes
(
entityid char(10),
recordType char(2),
primary key (entityid, recordType)
);

How do I get from here ...

rick,AL
rick,FR
rick,TR
rick,HS
joe,AL
joe,FU
Bob,FM

to a view or table that has this structure 

entityid,rectype1,rectype2,rectype3,rectype4,rectype5
rick,AL,FR,TR,HS,NULL
joe,AL,FU,NULL,NULL,NULL
bob,FM,NULL,NULL,NULL,NULL

using SQL.

if an entity had more than 5 record types, only the first 5 would be
put into the output table/view.

Rick





~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328357
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: (ot) SQL Question - flattening data

[Rick Root]

From the documentation, pivot tables seem to require aggregate
functions... The generic description would seem to work but the
examples make it difficult to see how.

But... I figured out a solution!  Using SQL Server's row_number() over
(partition by XXX order by XXX) I can make a subquery that returns
data like this

entityid,rownum,rectyp

And then run this query (tb901 is my primary table)

select
tb901.entityid,
R1.rectypcd as rectype1,
r2.rectypcd as rectype2,
r3.rectypcd as rectype3,
r4.rectypcd as rectype4,
r5.rectypcd as rectype5
from
tb901
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R1 on tb901.entityid=R1.entityid and R1.rownum=1
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R2 on tb901.entityid=R2.entityid and R2.rownum=2
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R3 on tb901.entityid=R3.entityid and R3.rownum=3
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R4 on tb901.entityid=R4.entityid and R4.rownum=4
left join
(
select row_number() over(partition by entityid order by 
rectypcd)
as rownum, entityid, rectypcd
from tb906
) R5 on tb901.entityid=R5.entityid and R5.rownum=5

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328377
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: (ot) SQL question...

[Dave Phillips]

Che,

Try this:

SELECT count(total) as sourcetotal, source
FROM (
select count(*) as total, source
from listings
group by source

union all

select count(*) as total, source
from speclistings
group by source

union all

select count(*) as total, source
from psportlistings
group by source
)
GROUP BY source
ORDER BY source DESC

Sincerely,

Dave Phillips
http://www.dave-phillips.com

-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 19, 2008 9:11 AM
To: CF-Talk
Subject: (ot) SQL question...

Hello all. I'm looking to output the total number of entries, grouped by
source from 3 tables. I'd like to modify the sql below so that each source
shows up only once, yet tablulates the totals from all of the tables. Any
ideas. Thanks, Che.

---

select count(*) as total, source
from listings
group by source

union all

select count(*) as total, source
from speclistings
group by source

union all

select count(*) as total, source
from psportlistings
group by source

order by total desc





~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311248
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: (ot) SQL question...

[Mark Kruger]

Che,

Well you could it inline... something like 



Select sum(t.total) as total, t.source 
FROM
(
select count(*) as total, source
from listings
group by source

union all

select count(*) as total, source
from speclistings
group by source

union all

select count(*) as total, source
from psportlistings
group by source
) t
Group by t.source
Order by total





~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311251
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: (ot) SQL question...

[Che Vilnonis]

Mark/Dave... thanks so much. That worked perfectly!

-Original Message-
From: Mark Kruger [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 19, 2008 10:19 AM
To: CF-Talk
Subject: RE: (ot) SQL question...

Che,

Well you could it inline... something like 



Select sum(t.total) as total, t.source
FROM
(
select count(*) as total, source
from listings
group by source

union all

select count(*) as total, source
from speclistings
group by source

union all

select count(*) as total, source
from psportlistings
group by source
) t
Group by t.source
Order by total







~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311254
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: SQL Question -- Order by a column's value?

[Brian Kotek]

I actually prefer to do this in the ORDER BY clause (keeping the ordering
logic in the ORDER BY instead of in the SELECT) but the end result is the
same. If you won't or can't add a sort column to the table, a CASE statement
is about the only other way to do this in the query itself.

On Jan 25, 2008 3:00 PM, Ian Skinner [EMAIL PROTECTED] wrote:

 Che Vilnonis wrote:
  I was trying to do that w/o adding another column. Can it be done?
 Yes, see Crow's, Charlie's or my post on using CASE to create an inline
 sort column with SQL.



 

~|
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:297531
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: SQL Question -- Order by a column's value?

[Charlie Griefer]

something like...

SELECT
 Colleges,
 CASE
  when Colleges = 'Harvard' THEN 1
  when Colleges = 'Princeton' THEN 2
  when Colleges = 'Dartmouth' THEN 3
 END AS collegeOrder
FROM
 myTable
ORDER BY
 collegeOrder

(not tested) :)

On Jan 25, 2008 10:41 AM, Che Vilnonis [EMAIL PROTECTED] wrote:
 Suppose I have a small set of data with a column named Colleges. Is there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


 

~|
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:297453
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: OT: SQL Question -- Order by a column's value?

[Che Vilnonis]

I was trying to do that w/o adding another column. Can it be done? 

-Original Message-
From: Todd [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 25, 2008 1:50 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?

Nope, add a sort_order column and sort your colleges appropriately.

On Jan 25, 2008 1:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is 
 there a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che




~|
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:297456
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: SQL Question -- Order by a column's value?

[Ian Skinner]

There is no way to do it with pure SQL alone.

Dominic

Well actually you can do in pure SQL.  SQL has code that can be used to create 
dynamic columns and values on the fly in your record set and then one can order 
on this set.  It sort of depends on whether the desire order is permanent or 
flexible.  I.E. sometimes one wants 'Harvard', 'Princeton' and 'Dartmouth' and 
another time one wants 'Princeton', 'Dartmouth' and 'Harvard'.


SELECT
  CASE college
 WHEN 'Harvard' THEN 1
 WHEN 'Princeton' THEN 2
 WHEN 'Dartmouth' THEN 3
  END AS sortCol

FROM aTable

ORDER BY sortCol

The exact syntax can very from database to database management system.




~|
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:297460
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: SQL Question -- Order by a column's value?

[Ian Skinner]

Che Vilnonis wrote:
 I was trying to do that w/o adding another column. Can it be done? 
Yes, see Crow's, Charlie's or my post on using CASE to create an inline 
sort column with SQL.



~|
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:297467
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: SQL Question -- Order by a column's value?

[Todd]

Sorry, add sort_order column and then do an ORDER BY sort_order and set all
the colleges in the appropriate sorting that you want it to be.

On Jan 25, 2008 1:49 PM, Todd [EMAIL PROTECTED] wrote:


 Nope, add a sort_order column and sort your colleges appropriately.


 On Jan 25, 2008 1:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

  Suppose I have a small set of data with a column named Colleges. Is
  there
  a way to write an ORDER BY statement to say something like...
  ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges
  'Dartmouth'???
 
  Just wondering... Che


http://www.web-rat.com/


~|
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:297451
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: SQL Question -- Order by a column's value?

[Todd]

Nope, add a sort_order column and sort your colleges appropriately.

On Jan 25, 2008 1:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is
 there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


~|
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:297450
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: OT: SQL Question -- Order by a column's value?

[Dawson, Michael]

Although you can do it as Charlie demonstrated, using CASE, Dominic's
solution is probably the best.

Put the data in a table where it belongs.  Then, that same data can be
reused for other purposes.

M!ke

-Original Message-
From: Dominic Watson [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 25, 2008 12:55 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?

Do you mean put them in a predifind order based on the college, other
than alphabetical?

If so, and if you have a lookup table for your colleges, you will have
to add a numerical column called 'Ordinal' (or something else) with
which you can set their order. Then simply order by that in your SQL
statement. There is no way to do it with pure SQL alone.

Dominic


~|
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:297461
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: SQL Question -- Order by a column's value?

[Crow T. Robot]

Yes, you can do this using case statements in your order by:

example:

select * from viewoffers where [EMAIL PROTECTED]

order by

case status

when 'active' then 1

when 'rejected' then 2

else 99

end



Of course, this is really a kludge.  The DB should be deisgned a little
better, but sometimes a kludge is what you need to get the job done. Not
everything can be elegant.

On Jan 25, 2008 12:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is
 there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


 

~|
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:297459
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: SQL Question -- Order by a column's value?

[Ian Skinner]

Che Vilnonis wrote:
 Suppose I have a small set of data with a column named Colleges. Is there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che
If  I understand your question correctly, you want to order a record set 
by first 'Harvard' record(s) then 'Princeton' record(s) and finally 
'Dartmouth' record(s).  If so yes, but you do most of the work in the 
SELECT clause.  Using logic functions in the SELECT clause such as CASE 
you can create dynamically create a sortable column with the appropriate 
values such as Harvard = 1, Princeton = 2 and Dartmouth = 3.  Then you 
just ORDER BY on this dynamic column and you the the result set you desire.

HTH
Because I don't have an example at my finger tips or remember the exact 
syntax off the top of my head to cobble one together this exact second.

Ian


~|
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:297452
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: SQL Question -- Order by a column's value?

[Dominic Watson]

Do you mean put them in a predifind order based on the college, other than
alphabetical?

If so, and if you have a lookup table for your colleges, you will have to
add a numerical column called 'Ordinal' (or something else) with which you
can set their order. Then simply order by that in your SQL statement. There
is no way to do it with pure SQL alone.

Dominic


On 25/01/2008, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is
 there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


 

~|
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:297454
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: SQL Question

[Kris Jones]

How about something like this:

SELECT
   F.pid,
   F.acrostic,
   F.recid,
   F.recordthread,
   F.aed_onset,
   F.d_form
FROM
   vfrm_sae F
WHERE NOT EXISTS (SELECT 1 FROM v_sae_jna_mr WHERE recid=F.recordthread)

Not sure it'll be much faster, but it's worth a try.

Cheers,
Kris

 The following query is slow.  I'd like to speed it up a bit.  Any
 suggestions would be appreciated.

 SELECT
F.pid,
F.acrostic,
F.recid,
F.recordthread,
F.aed_onset,
F.d_form
 FROM
vfrm_sae F
 LEFT OUTER JOIN
v_sae_jna_mr M
 ON
F.recordthread = M.i_recordThread
 WHERE
(M.i_recid IS NULL)

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274327
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: OT: SQL Question

[Bader, Terrence C CTR MARMC, 231]

whats not being asked is how big is the dataset, what type of hardware, how
do you know its running slow?  compared to what?

I mean if you have a million records on a Pentium 2 with 128m of ram, your
going to have less then average response time.  also are the CF server and
DB server on the same LAN?

I think the sql statement you have is just about as straight forward as you
are going to get.  how long does a straight SELECT * FROM F INNER JOIN M
taking?? 

-Original Message-
From: Kris Jones [mailto:[EMAIL PROTECTED] 
Sent: Monday, April 02, 2007 11:43
To: CF-Talk
Subject: Re: OT: SQL Question

How about something like this:

SELECT
   F.pid,
   F.acrostic,
   F.recid,
   F.recordthread,
   F.aed_onset,
   F.d_form
FROM
   vfrm_sae F
WHERE NOT EXISTS (SELECT 1 FROM v_sae_jna_mr WHERE recid=F.recordthread)

Not sure it'll be much faster, but it's worth a try.

Cheers,
Kris

 The following query is slow.  I'd like to speed it up a bit.  Any 
 suggestions would be appreciated.

 SELECT
F.pid,
F.acrostic,
F.recid,
F.recordthread,
F.aed_onset,
F.d_form
 FROM
vfrm_sae F
 LEFT OUTER JOIN
v_sae_jna_mr M
 ON
F.recordthread = M.i_recordThread
 WHERE
(M.i_recid IS NULL)



~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. 
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274329
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: SQL Question

[Dean Lawrence]

Has the i_recid field in the v_sae_jna_mr table been indexed? If your
table is very large, this could slow your performance.

Dean
-- 
__
Dean Lawrence, CIO/Partner
Internet Data Technology
888.GET.IDT1 ext. 701 * fax: 888.438.4381
http://www.idatatech.com/
Corporate Internet Development and Marketing Specialists

~|
Macromedia ColdFusion MX7
Upgrade to MX7  experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274332
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: SQL Question

[Jochem van Dieten]

Jerry Barnes wrote:
 The following query is slow.  I'd like to speed it up a bit.  Any
 suggestions would be appreciated.
 
 
 SELECT
F.pid,
F.acrostic,
F.recid,
F.recordthread,
F.aed_onset,
F.d_form
 FROM
vfrm_sae F
 LEFT OUTER JOIN
v_sae_jna_mr M
 ON
F.recordthread = M.i_recordThread
 WHERE
(M.i_recid IS NULL)

How does the execution plan look?

ISTM you want a mergejoin with vfrm_sae as the driving table and 
v_sae_jna_mr as the inner table. If your execution plan shows 
differently, make sure you have clustered indexes on recordthread and 
i_recordThread and try again.

Jochem

~|
Macromedia ColdFusion MX7
Upgrade to MX7  experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274342
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: SQL Question

[Jerry Barnes]

Thanks for your replies.

I didn't get a chance to play with anything this afternoon due to
meetings.  I'll try to implement something tommorrow and fill you all
in on the results.

The recordsets aren't that big.  About 9k records in one table and 3k
in the other.

~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade  see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274383
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: SQL question

[Jochem van Dieten]

Chad Gray wrote:
 
 I am basically doing a query on a table of catalogs and elements 
 inside of the catalog.  Each element has a history of status changes 
 (new, in progress, done etc).

So each element always has a corresponding value in the history table.


 SELECT c.CatalogID, c.CatalogName, e.*, es.ElementStatus, es.
 ElementStatusDate, es.UserName
 FROM Catalogs as c
 LEFT JOIN Elements as e ON c.CatalogID = e.CatalogID
 LEFT JOIN (
   SELECT MAX(ElementStatusHistoryID) as MaxESHID, ElementID
   FROM ElementStatusHistory
   GROUP BY ElementID
 ) as maxESH ON (e.ElementID = maxESH.ElementID)
 LEFT JOIN (
   SELECT ElementStatusHistoryID, ElementStatus, ElementStatusDate, 
 UserName
   FROM ElementStatusHistory
 ) as es ON (es.ElementStatusHistoryID = maxESH.MaxESHID)
 WHERE c.CatalogID = 10

Try:

SELECT
  c.CatalogID,
  c.CatalogName,
  e.*, es.ElementStatus,
  es. ElementStatusDate,
  es.UserName
FROM
Catalogs C
LEFT JOIN
(
SELECT *
FROM
(
SELECT MAX(ElementStatusHistoryID) as MaxESHID, 
ElementID
FROM ElementStatusHistory
GROUP BY ElementID
) tmp
INNER JOIN
ElementStatusHistory ON es.ElementStatusHistoryID = 
tmp.MaxESHID
INNER JOIN
Elements ON e.ElementID = tmp.ElementID
) E ON C.CatalogID = E.CatalogID

Jochem

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262232
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT SQL question

[Qasim Rasheed]

I think you can write a UDF to validate email addresses. Here is link 

http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm


On Tue, 8 Feb 2005 12:55:42 -0600, Eric Creese [EMAIL PROTECTED] wrote:
 I want to verify email addresses that are entered into one of my apps. 
 Unfortunately I already inherited close to 100k email address. So I want to 
 do the following in SQL via a stored procedure so I can write the bad 
 addresses out to an error table. Need to check if there is an @ sign, if the 
 TDL is valid from the list I have from ICANN. When I check through each like 
 statement but that is not going to work. Any other ideas? This can not be 
 done through a CF page but eventually the result will b posted to one.
 
 SELECT DISTINCT personid,email
 FROM People
 WHERE MbrExpireDate  '2/1/2005'
 AND (email not like '[EMAIL PROTECTED]'
 OR email NOT LIKE '%.AC'
 
 OR email NOT LIKE '%.ZW')
 
 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193700
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: OT SQL question

[Eric Creese]

Thanks but I also need to try to test the TDLs like .com, .net, .uk...

-Original Message-
From: Qasim Rasheed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 08, 2005 1:19 PM
To: CF-Talk
Subject: Re: OT SQL question


I think you can write a UDF to validate email addresses. Here is link 

http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm


On Tue, 8 Feb 2005 12:55:42 -0600, Eric Creese [EMAIL PROTECTED] wrote:
 I want to verify email addresses that are entered into one of my apps. 
 Unfortunately I already inherited close to 100k email address. So I want to 
 do the following in SQL via a stored procedure so I can write the bad 
 addresses out to an error table. Need to check if there is an @ sign, if the 
 TDL is valid from the list I have from ICANN. When I check through each like 
 statement but that is not going to work. Any other ideas? This can not be 
 done through a CF page but eventually the result will b posted to one.
 
 SELECT DISTINCT personid,email
 FROM People
 WHERE MbrExpireDate  '2/1/2005'
 AND (email not like '[EMAIL PROTECTED]'
 OR email NOT LIKE '%.AC'
 
 OR email NOT LIKE '%.ZW')
 
 



~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193708
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: OT SQL question

[Jochem van Dieten]

Eric Creese wrote:
 I want to verify email addresses that are entered into one of my apps. 
 Unfortunately I already inherited close to 100k email address. So I want to 
 do the following in SQL via a stored procedure so I can write the bad 
 addresses out to an error table. Need to check if there is an @ sign, if the 
 TDL is valid from the list I have from ICANN. When I check through each like 
 statement but that is not going to work. Any other ideas? This can not be 
 done through a CF page but eventually the result will b posted to one.

First create a table with all your TLDs and then:
INSERT INTO bademail (personid,email)
SELECT DISTINCT personid, email
FROM People LEFT JOIN tlds ON (people.email LIKE '[EMAIL PROTECTED]' || 
tlds.tld)
WHERE MbrExpireDate  '2/1/2005'
AND tlds.tld IS NULL

This presumes standard SQL, i.e. || is the concatenation operator 
and _ means exactly one character.

Jochem

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193713
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: OT SQL question-SOLVED

[Eric Creese]

danke!

-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 08, 2005 2:12 PM
To: CF-Talk
Subject: Re: OT SQL question


Eric Creese wrote:
 I want to verify email addresses that are entered into one of my apps. 
 Unfortunately I already inherited close to 100k email address. So I want to 
 do the following in SQL via a stored procedure so I can write the bad 
 addresses out to an error table. Need to check if there is an @ sign, if the 
 TDL is valid from the list I have from ICANN. When I check through each like 
 statement but that is not going to work. Any other ideas? This can not be 
 done through a CF page but eventually the result will b posted to one.

First create a table with all your TLDs and then:
INSERT INTO bademail (personid,email)
SELECT DISTINCT personid, email
FROM People LEFT JOIN tlds ON (people.email LIKE '[EMAIL PROTECTED]' || 
tlds.tld)
WHERE MbrExpireDate  '2/1/2005'
AND tlds.tld IS NULL

This presumes standard SQL, i.e. || is the concatenation operator 
and _ means exactly one character.

Jochem



~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193728
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: OT: Sql question

[Umer Farooq]

John Munyan wrote:
 I have a question about how a relationship would be best modeled in SQL.  
 Currently I have a hiking website, which hosts trail reviews.  People can add 
 their own comments which are associated with the hike.
  
 For instance maybe I hiked snow lake on 12/1/05 and also 6/1/05.  However, 
 the user review gets associated with the instance of the hike either on 
 12/1/05. or 6/1/05.  I want to combine these reviews so that they are 
 associated with both.  I think a group ID could be assigned so this could be 
 accomplished.

Hmm.. wouldn't just querying to figure out if there is another review 
from the same user.. for a same trail.. take care of that.. am I missing 
something here..

 However...
  
 The real problem however is how to model something like this where one hike 
 is a subset or superset of another.  Take for instance this example.
  
 TrailHead---3miles--Snowlake2milesGemlake---1mile---WrightMountain.
  
 I would like  the Snow lake reviews to be shown when either GemLake is viewed 
 or Wright Mountain.  However, I wouldn't want reviews for Gem Lake when the 
 user is only intending to go to Snowlake.  Thus the problem is how one would 
 handle this superset/subset association of reviews.
  
 If anyone has any thoughts about how this would be handled I would be 
 grateful for the advice.

Simplest thing that comes to my mind is to create a another table.. 
which holds... review relationship between the trails..

snowLake   :
gemlake: snowLake,trailHead
wrightMountain : snowLake
trailHead  : gemLake

-- 
Umer Farooq
Octadyne Systems
+1 (519) 489-1119 voice
+1 (519) 635-2795 mobile
+1 (530) 326-3586 fax


WEB SOLUTIONS FOR NON-PROFIT ORGANIZATION:
http://www.Non-ProfitSites.biz


WARNING: --- The information contained in 
this document and attachments is confidential and intended only for the 
person(s) named above. If you are not the  intended recipient you are 
hereby notified that any disclosure, copying, distribution, or any other 
use of the information is strictly prohibited.  If you have received 
this document by mistake, please notify the sender immediately and 
destroy this document and attachments without making any copy of any kind.


~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193139
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: OT: Sql question

[John Munyan]

Yes, the crux of the question is how to handle the subset question. In the 
below example a review left for SnowLake would be availble for the Snowlake 
Hike only.  If I user left a review for Gem lake, then the review should be 
available for SnowLake or Gemlake since you pass by Snow lake on your way to 
Gem lake.  Similiarly, Wright Mountain, would show reviews left for Wright 
Mountain, but also for Gem Lake and Snow lake since you pass by them en route 
to Wright Mountain.
 
Thus how do you handle nesting of these elements where one is a subset of 
another...
 
Thanks,

John



From: Umer Farooq [mailto:[EMAIL PROTECTED]
Sent: Fri 2/4/2005 12:54 PM
To: CF-Talk
Subject: Re: OT: Sql question



John Munyan wrote:
 I have a question about how a relationship would be best modeled in SQL.  
 Currently I have a hiking website, which hosts trail reviews.  People can add 
 their own comments which are associated with the hike.
 
 For instance maybe I hiked snow lake on 12/1/05 and also 6/1/05.  However, 
 the user review gets associated with the instance of the hike either on 
 12/1/05. or 6/1/05.  I want to combine these reviews so that they are 
 associated with both.  I think a group ID could be assigned so this could be 
 accomplished.

Hmm.. wouldn't just querying to figure out if there is another review
from the same user.. for a same trail.. take care of that.. am I missing
something here..

 However...
 
 The real problem however is how to model something like this where one hike 
 is a subset or superset of another.  Take for instance this example.
 
 TrailHead---3miles--Snowlake2milesGemlake---1mile---WrightMountain.
 
 I would like  the Snow lake reviews to be shown when either GemLake is viewed 
 or Wright Mountain.  However, I wouldn't want reviews for Gem Lake when the 
 user is only intending to go to Snowlake.  Thus the problem is how one would 
 handle this superset/subset association of reviews.
 
 If anyone has any thoughts about how this would be handled I would be 
 grateful for the advice.

Simplest thing that comes to my mind is to create a another table..
which holds... review relationship between the trails..

snowLake   :
gemlake: snowLake,trailHead
wrightMountain : snowLake
trailHead  : gemLake

--
Umer Farooq
Octadyne Systems
+1 (519) 489-1119 voice
+1 (519) 635-2795 mobile
+1 (530) 326-3586 fax


WEB SOLUTIONS FOR NON-PROFIT ORGANIZATION:
http://www.Non-ProfitSites.biz


WARNING: --- The information contained in
this document and attachments is confidential and intended only for the
person(s) named above. If you are not the  intended recipient you are
hereby notified that any disclosure, copying, distribution, or any other
use of the information is strictly prohibited.  If you have received
this document by mistake, please notify the sender immediately and
destroy this document and attachments without making any copy of any kind.




~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193143
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: OT: Sql question

[Umer Farooq]

Well.. oneway to do it is to create a new relationship table.. i.e

tblRelatedTrails
 relationID
 trailID
 relatedTrailID

and when doing a select for review you can do a sub select on the 
relatedTrails table.. and use IN()

anotherway is to use the geo info of the trails.. and select.. all 
reviews falling in a radius.

John Munyan wrote:
 Yes, the crux of the question is how to handle the subset question. In the 
 below example a review left for SnowLake would 
be availble for the Snowlake Hike only.  If I user left a review for Gem 
lake, then the review should be available for SnowLake or Gemlake since 
you pass by Snow lake on your way to Gem lake.  Similiarly, Wright 
Mountain, would show reviews left for Wright Mountain, but also for Gem 
Lake and Snow lake since you pass by them en route to Wright Mountain.
  
 Thus how do you handle nesting of these elements where one is a subset of 
 another...
  
 Thanks,
 
 John
 


-- 
Umer Farooq
Octadyne Systems
+1 (519) 489-1119 voice
+1 (519) 635-2795 mobile
+1 (530) 326-3586 fax


WEB SOLUTIONS FOR NON-PROFIT ORGANIZATION:
http://www.Non-ProfitSites.biz


WARNING: --- The information contained in 
this document and attachments is confidential and intended only for the 
person(s) named above. If you are not the  intended recipient you are 
hereby notified that any disclosure, copying, distribution, or any other 
use of the information is strictly prohibited.  If you have received 
this document by mistake, please notify the sender immediately and 
destroy this document and attachments without making any copy of any kind.


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193150
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: ot: sql question

[Qasim Rasheed]

As far as I know you cannot.

- Original Message -
From: Tony Weeg [EMAIL PROTECTED]
Date: Wed, 22 Sep 2004 09:59:03 -0400
Subject: ot: sql question
To: CF-Talk [EMAIL PROTECTED]

is it true that we cannot use go in sql statements using cfquery?

 -- 
 tony

 Tony Weeg

 macromedia certified cold fusion developer
 email: tonyweeg [at] gmail [dot] com
 blog: http://www.revolutionwebdesign.com/blog/
 cool tool: http://www.antiwrap.com
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: ot: sql question

[Tony Weeg]

yeah, i figured that, i made sep. dsn's and its all good now :)

thanks.
tony

On Wed, 22 Sep 2004 15:21:53 -0400, Qasim Rasheed
[EMAIL PROTECTED] wrote:
 As far as I know you cannot.
 
 
 
 
 - Original Message -
 From: Tony Weeg [EMAIL PROTECTED]
 Date: Wed, 22 Sep 2004 09:59:03 -0400
 Subject: ot: sql question
 To: CF-Talk [EMAIL PROTECTED]
 
 is it true that we cannot use go in sql statements using cfquery?
 
 --
 tony
 
 Tony Weeg
 
 macromedia certified cold fusion developer
 email: tonyweeg [at] gmail [dot] com
 blog: http://www.revolutionwebdesign.com/blog/
 cool tool: http://www.antiwrap.com
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: ot: sql question

[Jochem van Dieten]

Tony Weeg wrote:
 how would I use HAVING to select out where the difference between
 reportsInDatabase and DistinctTimes is above 15%

 select r.IpAddressNumber, v.VehicleIp, 
 	Count(r.ReportId) as ReportsInDatabase, 
 	Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName
 from reportsView r
 INNER JOIN 
 	vehicletable v
 	on v.IpAddressNumber = r.IpAddressNum
 INNER JOIN
 	companyTable c
 	on v.companyIdNumber = c.companyIdNumber
 where r.currentDate between '09-29-2003 00:00:00.000'and '09-29-2003
 23:59:59.999'
 	Group By v.IpAddressNumber, r.IpAddressNum, v.VehicleName,
 v.VehicleIp, c.companyName
 	Order by ReportsInDatabase DESC

SELECT
	r.IpAddressNumber,
	v.VehicleIp,
	Count(r.ReportId) as ReportsInDatabase,
	Count(DISTINCT Time) as DistinctTimes,
	v.VehicleName,
	c.companyName
FROM
	reportsView r INNER JOIN vehicletable v
		ON v.IpAddressNumber = r.IpAddressNum
	INNER JOIN companyTable c
		ON v.companyIdNumber = c.companyIdNumber
WHERE
	r.currentDate BETWEEN '09-29-2003 00:00:00.000'
		AND '09-29-2003 23:59:59.999'
GROUP BY
	v.IpAddressNumber,
	r.IpAddressNum,
	v.VehicleName,
	v.VehicleIp,
	c.companyName
HAVING
	Abs((ReportsInDatabase-DistinctTimes)/ReportsInDatabase)
		 0.15
ORDER BY
	ReportsInDatabase DESC

Jochem



 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: ot: sql question

[Tony Weeg]

thank you jochem, that makes sense, I was close, but forgot about the abs
function, which I presume is for absolute number

anyway, now I get this

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ReportsInDatabase'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DistinctTimes'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ReportsInDatabase'. 


...tony

tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337

-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 01, 2003 11:10 AM
To: CF-Talk
Subject: Re: ot: sql question

Tony Weeg wrote:
 how would I use HAVING to select out where the difference between 
 reportsInDatabase and DistinctTimes is above 15%

 select r.IpAddressNumber, v.VehicleIp,
 Count(r.ReportId) as ReportsInDatabase, Count(DISTINCT Time) as 
 DistinctTimes, v.VehicleName, c.companyName from reportsView r INNER 
 JOIN vehicletable v on v.IpAddressNumber = r.IpAddressNum INNER JOIN 
 companyTable c on v.companyIdNumber = c.companyIdNumber where 
 r.currentDate between '09-29-2003 00:00:00.000'and '09-29-2003 
 23:59:59.999'
 Group By v.IpAddressNumber, r.IpAddressNum, v.VehicleName, 
 v.VehicleIp, c.companyName Order by ReportsInDatabase DESC

SELECT
r.IpAddressNumber,
v.VehicleIp,
Count(r.ReportId) as ReportsInDatabase,
Count(DISTINCT Time) as DistinctTimes,
v.VehicleName,
c.companyName
FROM
reportsView r INNER JOIN vehicletable v
ON v.IpAddressNumber = r.IpAddressNum
INNER JOIN companyTable c
ON v.companyIdNumber = c.companyIdNumber WHERE r.currentDate BETWEEN
'09-29-2003 00:00:00.000'
AND '09-29-2003 23:59:59.999'
GROUP BY
v.IpAddressNumber,
r.IpAddressNum,
v.VehicleName,
v.VehicleIp,
c.companyName
HAVING
Abs((ReportsInDatabase-DistinctTimes)/ReportsInDatabase)
 0.15
ORDER BY
ReportsInDatabase DESC

Jochem






 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: OT: SQL Question

[Stephen Hait]

 I have a ColdFusion app that is dynamically managing a SQL Server 2K
 database.   What I am running into is the need to change a column
 data type - specifically an ntext data type.  Anybody got any
 suggestions?
 
 It appears that I cannot used ALTER TABLE/COLUMN with an ntext data
 type, so what other options are there?   Is there a clean way to
 create a new nvarchar column for example, then copy and truncate all
 of the info in the ntext column, then delete the ntext column?

You are correct that you cannot employ ALTER TABLE/ALTER 
COLUMN when the column datatype is text. 

One solution might be to create a new table, insert data from the 
old table, drop the old table and then rename the new table. 

I think this may also be how this is accomplished when you 
change the datatype of a text column from within EM although 
I'm not really sure about this.

Stephen

 Thanks for any pointers.
 -- Jeff
 
 
 ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
 Unsubscribe:
 http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=2137.2057
 .4
 
 Your ad could be here. Monies from ads go to support these lists and
 provide more resources for the community.
 http://www.fusionauthority.com/ads.cfm


~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

RE: OT SQL Question

[webguy]

SQL server ? use a format like this..

INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
   SUM(titles.price * sales.qty)
   FROM authors INNER JOIN titleauthor
  ON authors.au_id = titleauthor.au_id INNER JOIN titles
  ON titleauthor.title_id = titles.title_id INNER JOIN sales
  ON titles.title_id = sales.title_id
   WHERE authors.au_id like ''8%''
   GROUP BY authors.au_id, authors.au_lname
')

wg

-Original Message-
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: 03 March 2003 14:36
To: CF-Talk
Subject: OT SQL Question


Can someone assist me with a quick SQL statement.

I have a table with 3 columns:  A, B, C (A would be the primary key)
I need to swap the values in column B with the values in column C and vice
versa.

TIA,

Mike




~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: OT SQL Question

[Tangorre, Michael]

SQL 7

does this still apply?


-Original Message-
From: webguy [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2003 9:42 AM
To: CF-Talk
Subject: RE: OT SQL Question


SQL server ? use a format like this..

INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
   SUM(titles.price * sales.qty)
   FROM authors INNER JOIN titleauthor
  ON authors.au_id = titleauthor.au_id INNER JOIN titles
  ON titleauthor.title_id = titles.title_id INNER JOIN sales
  ON titles.title_id = sales.title_id
   WHERE authors.au_id like ''8%''
   GROUP BY authors.au_id, authors.au_lname
')

wg

-Original Message-
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: 03 March 2003 14:36
To: CF-Talk
Subject: OT SQL Question


Can someone assist me with a quick SQL statement.

I have a table with 3 columns:  A, B, C (A would be the primary key)
I need to swap the values in column B with the values in column C and vice
versa.

TIA,

Mike





~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: OT SQL Question

[webguy]

yeap actually simplier...

INSERT t1
SELECT a as a , b as c ,c as b
FROM t1

TEST b4 you do it

WG

-Original Message-
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: 03 March 2003 14:44
To: CF-Talk
Subject: RE: OT SQL Question


SQL 7

does this still apply?


-Original Message-
From: webguy [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2003 9:42 AM
To: CF-Talk
Subject: RE: OT SQL Question


SQL server ? use a format like this..

INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
   SUM(titles.price * sales.qty)
   FROM authors INNER JOIN titleauthor
  ON authors.au_id = titleauthor.au_id INNER JOIN titles
  ON titleauthor.title_id = titles.title_id INNER JOIN sales
  ON titles.title_id = sales.title_id
   WHERE authors.au_id like ''8%''
   GROUP BY authors.au_id, authors.au_lname
')

wg

-Original Message-
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: 03 March 2003 14:36
To: CF-Talk
Subject: OT SQL Question


Can someone assist me with a quick SQL statement.

I have a table with 3 columns:  A, B, C (A would be the primary key)
I need to swap the values in column B with the values in column C and vice
versa.

TIA,

Mike






~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: OT SQL Question

[Cantrell, Adam]

Are you talking about a one time query to change the values stored in the
DB?


UPDATE tableName
SET 
tableName.B = tableName.C,
tableName.C = tableName.B
WHERE tableName.A = tableName.A

Adam.




 -Original Message-
 From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
 Sent: Monday, March 03, 2003 8:36 AM
 To: CF-Talk
 Subject: OT SQL Question
 
 
 Can someone assist me with a quick SQL statement.
  
 I have a table with 3 columns:  A, B, C (A would be the primary key)
 I need to swap the values in column B with the values in 
 column C and vice versa.
  
 TIA,
  
 Mike
  
  
 
 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT SQL Question

[Stephen Hait]

 Can someone assist me with a quick SQL statement.
 
 I have a table with 3 columns:  A, B, C (A would be the primary key)
 I need to swap the values in column B with the values in column C
 and vice versa.

This should work with MS SQL assuming columns b and c are 
the same data type:
UPDATE t1
SET b=c, c=b

Stephen
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: OT: SQL Question

[Tony Carcieri]

Thanks Everyone! I appreciate the help!

Thanks,
Tony

-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 09, 2002 7:26 PM
To: CF-Talk
Subject: Re: OT: SQL Question


Tony Carcieri wrote:
 Hi all,

 Here's what I want to do:
 UPDATE tablename
 SET column = 0
 WHERE ID = ???

 I want to specify a range of numbers (like 100-200) and increment it by 2.
 So, 100, 102,104etc would only be updated and the rest wouldn't.

WHERE
ID BETWEEN 100 AND 200
AND MOD(ID, 2) = 0

Jochem



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Re: OT: SQL Question

[Jochem van Dieten]

Tony Carcieri wrote:
 Hi all,
 
 Here's what I want to do:
 UPDATE tablename
 SET column = 0
 WHERE ID = ???
 
 I want to specify a range of numbers (like 100-200) and increment it by 2.
 So, 100, 102,104etc would only be updated and the rest wouldn't.

WHERE
ID BETWEEN 100 AND 200
AND MOD(ID, 2) = 0

Jochem


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

RE: OT: SQL question

[Alistair Davidson]

Off the top of my head, in SQL Server you  can do it like this : 

SELECT PartNumber, 
'FirstBitOfPartNumber' = LEFT( PartNumber, FIND( '.', PartNumber ) )

FROM blahblahblah

If you wanted to just get matching rows, you can use that as a join
condition, e.g.

SELECT PartNumber

FROM Table1 INNER JOIN Table2 ON Table2.PartNumber = LEFT(
Table1.PartNumber, FIND( '.', Table1.PartNumber ) )

WHERE blah blah blah

Hope that helps

Alistair 

Alistair Davidson 
Application Developer
www.smartgroups.com
Freeserve.com PLC


-Original Message-
From: Alex [mailto:[EMAIL PROTECTED]] 
Sent: 29 July 2002 21:27
To: CF-Talk
Subject: Re: OT: SQL question

Great. You did not provide your database or setup. I can only assume you
are working on a mainframe hitting DB2.

On Mon, 29 Jul 2002, Phillip B wrote:

 I need to do this and don't know where to start.

 Compare part of a part number in one table to another table. The part
numbers look like this.

 12345.123
 12345.234
 12345.654
 02nt911dc.123
 02nt911dc.054
 02thq5
 02thq7

 The part before the dot is all I want to use to compare to the other
table which will only contain the first part. It would be something like
this.

 12345.123 = 12345
 12345.234 = 12345
 12345.654 = 12345
 02nt911dc.123 = 02nt911dc
 02nt911dc.054 = 02nt911dc
 02thq5 = 02thq5
 02thq7 = 02thq7

 How would I do that using just sql statements?

 Maybe when I have the server import I could have it create an extra
column that can contain a trimmed version of the part number. A master
number of sorts. Just a thought.


 Thanks

 Phillip Broussard
 Tracker Marine Group
 417-873-5957


 

__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: OT: SQL question

[Alex]

Great. You did not provide your database or setup. I can only assume you
are working on a mainframe hitting DB2.

On Mon, 29 Jul 2002, Phillip B wrote:

 I need to do this and don't know where to start.

 Compare part of a part number in one table to another table. The part numbers look 
like this.

 12345.123
 12345.234
 12345.654
 02nt911dc.123
 02nt911dc.054
 02thq5
 02thq7

 The part before the dot is all I want to use to compare to the other table which 
will only contain the first part. It would be something like this.

 12345.123 = 12345
 12345.234 = 12345
 12345.654 = 12345
 02nt911dc.123 = 02nt911dc
 02nt911dc.054 = 02nt911dc
 02thq5 = 02thq5
 02thq7 = 02thq7

 How would I do that using just sql statements?

 Maybe when I have the server import I could have it create an extra column that can 
contain a trimmed version of the part number. A master number of sorts. Just a 
thought.


 Thanks

 Phillip Broussard
 Tracker Marine Group
 417-873-5957


 
__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: OT: SQL Question (Access) the solution

[Mike Kear]

Well DOH!!   Thanks to all of you who pointed out to me the blindingly
obvious - all I had to do was join the tables.  Jeez, I knew that .. I had
just momentarily forgotten is all.  Bob's answer was one of many who pointed
out what I should obviously have known and recalled.

The only extra piece that was needed was to allow for the fact that there
were several answers for some questions, so I had to add Distinct to the SQL
like so:
SELECT DISTINCT
 HNTQuestion.QuestionID,
 HNTQuestion.Category,
 HNTQuestion.QuestionShort,
 HNTCategories.HNTCategoryID,
 HNTCategories.HNTCategoryName

FROM  HNTQuestion, HNTCategories, tblAnswers

   WHERE HNTQuestion.Category  = HNTCategories.HNTCategoryID
   AND   HNTQuestion.QuestionID = tblAnswers.ANSQuestionID

   ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort



Thanks a lot for all your patience and help folks.

Cheers,
Mike Kear
Windsor, NSW, Australia
AFP WebWorks





-Original Message-
From: Bob Silverberg [[EMAIL PROTECTED]]

Just add the tblAnswers table to your join, like so:

SELECT
 HNTQuestion.QuestionID,
 HNTQuestion.Category,
 HNTQuestion.QuestionShort,
 HNTCategories.HNTCategoryID,
 HNTCategories.HNTCategoryName

FROM  HNTQuestion, HNTCategories, tblAnswers

   WHERE HNTQuestion.Category  = HNTCategories.HNTCategoryID
   AND   HNTQuestion.QuestionID = tblAnswers.ANSQuestionID

   ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort

Bob


-Original Message-
From: Michael Kear [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 5:33 PM
To: CF-Talk
Subject: OT: SQL Question (Access)


I'm sorry if this is off-topic, but I'm hoping for some help from people
who know more about SQL than I do ...

I have a hints'n'tips section on one of my sites, and it has 3 tables -
tblCategories, tblQuestions and tblAnswers.  I want to have an index page
that lists the questions in their categories, so you click on the question
and a window opens with all the answers to that question.

Here's the bit I am having trouble with .. A few questions don't have any
answers yet - for example some haven't been loaded up yet.   How do I get
SQL to retrieve only the questions that have answers in the Answers table,
leaving out any questions that have no answers?

Here's the SQL I'm using now, that returns all the questions (if it's
relevant here, the database is Access2000):

cfquery name="getquestions" datasource="#dsn#"
SELECT
 HNTQuestion.QuestionID,
 HNTQuestion.Category,
 HNTQuestion.QuestionShort,
 HNTCategories.HNTCategoryID,
 HNTCategories.HNTCategoryName

FROM  HNTQuestion, HNTCategories

   WHERE HNTQuestion.Category  = HNTCategories.HNTCategoryID

   ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort
/cfquery


An answer is linked to its associated question with a field ANSQuestionID
in the table tblAnswers which has the same value as the field
HNTQuestion.QuestionID.


Can anyone help me please?

Cheers,
Mike Kear
Windsor, NSW, Australia
AFP Webworks.
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists




Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: OT: SQL Question

[Gieseman, Athelene]

That was it!  Thank you!  

-Original Message-
From: Bud [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 16, 2001 4:18 PM
To: CF-Talk
Subject: Re: OT: SQL Question


On 1/16/01, Gieseman, Athelene penned:
cfquery datasource="IS_Budget" name="SaveInvoiceQry"
   INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date,
Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount)
   values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#',
'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Notes#',
#Inv_Amount#)
/cfquery

I'm sure I'm not seeing something obvious.  But it looks to me like there
are 10 items for each of the values and table columns.  Can anyone see what
I'm not?

One thing to look for is columns that don't have single quotes around 
them. If you input an #Inv_Amount# with a comma in it, like 1,000.00, 
it will think that is 2 fields, because of the comma. Try using:

#rereplace(Inv_Amount, "[^0-9\.]+", "", "ALL")#

Or even just:

#replace(Inv_Amount, ",", "", "ALL")#
-- 

Bud Schneehagen - Tropical Web Creations

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: OT: SQL Question

[Bud]

On 1/16/01, Gieseman, Athelene penned:
cfquery datasource="IS_Budget" name="SaveInvoiceQry"
   INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date,
Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount)
   values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#',
'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Notes#',
#Inv_Amount#)
/cfquery

I'm sure I'm not seeing something obvious.  But it looks to me like there
are 10 items for each of the values and table columns.  Can anyone see what
I'm not?

One thing to look for is columns that don't have single quotes around 
them. If you input an #Inv_Amount# with a comma in it, like 1,000.00, 
it will think that is 2 fields, because of the comma. Try using:

#rereplace(Inv_Amount, "[^0-9\.]+", "", "ALL")#

Or even just:

#replace(Inv_Amount, ",", "", "ALL")#
-- 

Bud Schneehagen - Tropical Web Creations

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: OT: SQL Question

[Bud]

On 1/16/01, Gieseman, Athelene penned:
cfquery datasource="IS_Budget" name="SaveInvoiceQry"
   INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date,
Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount)
   values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#',
'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Notes#',
#Inv_Amount#)
/cfquery

I'm sure I'm not seeing something obvious.  But it looks to me like there
are 10 items for each of the values and table columns.  Can anyone see what
I'm not?

One thing to look for is columns that don't have single quotes around 
them. If you input an #Inv_Amount# with a comma in it, like 1,000.00, 
it will think that is 2 fields, because of the comma. Try using:

#rereplace(Inv_Amount, "[^0-9\.]+", "", "ALL")#

Or even just:

#replace(Inv_Amount, ",", "", "ALL")#
-- 

Bud Schneehagen - Tropical Web Creations

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: ot: sql question

[David Shadovitz]

This hurts to look at.  Is that the entire table?  Or are there
additional fields that make those rows unique?  If it's the entire table,
why do you have identical rows, and why do you want to retain them?  If
there's more to the table, extend your 'where' clause to identify the
unique row that you want to delete.

I suppose you could delete all of these rows and then reinsert one, with
a CFTRANSACTION around the delete and insert.

Also, if the id's are numeric, omit the quotes.

-David

On Wed, 27 Sep 2000 13:58:57 -0500 Gavin Myers
[EMAIL PROTECTED] writes:
 here's what i'm doing
 
 delete from class_registration
 where users_id='123123'
 and class_id='4242424'
 
 here's what the table looks like
 
 name  users_idclass_id
 gavin 123123  4242424
 gavin 123123  4242424
 gavin 123123  4242424
 gavin 123123  4242424
 
 what i want to do is delete only 1 of those rows
 
 suggestions?
 
 thanks,
 Gavin

YOU'RE PAYING TOO MUCH FOR THE INTERNET!
Juno now offers FREE Internet Access!
Try it today - there's no risk!  For your FREE software, visit:
http://dl.www.juno.com/get/tagj.
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: OT - SQL question

[Pete Freitag]

SELECT DISTINCT Name, OrderNum
FROM tablename

___
Pete Freitag
CFDEV.COM
Cold Fusion Developer Resources
http://www.cfdev.com/

-Original Message-
From: PC [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 03, 2000 4:45 PM
To: [EMAIL PROTECTED]
Subject: OT - SQL question


Hey ...

How do you do a query where all rows returned except in the case of
duplicates, based on a name column, in which only the first is returned.

So the query run against the following rows:

NameOrderNum
Fred21
Beth36
Fred76

Would return :
NameOrderNum
Fred 21
Beth 36

Much thanks!!



--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.