|
 |
asp_databases thread: quickSQL question.
Message #1 by "Philip Sayers" <philipsayers@m...> on Wed, 28 Nov 2001 16:13:24 -0500
|
|
I?m trying to format a recordset to send to my webpage. I?m not sure
how to go about this.
I have a parent table which holds a list of days.
The child table holds a list of cities and a key from the parent table.
It?s for a travel agent. On any particular day, the agent can have
several tours running. Each tour goes to one city. I?d like to get my
output as a two field recordset. The first field holding the day and
the second field as a comma delimited list of the city names from the
child table.
I think I need a nested SELECT, but I?m not sure exactly how to form it.
Can someone point me in the right direction?
Thx
Phil
Message #2 by dont worry <aspmailbox@y...> on Wed, 28 Nov 2001 14:07:59 -0800 (PST)
|
|
Select cities
From ToursTbl
Where Day IN
(Select day from DaysTbl where day = thisdayplease)
Its a subquery, hope it helps.
DW
--- Philip Sayers <philipsayers@m...> wrote:
> I?m trying to format a recordset to send to my
> webpage. I?m not sure
> how to go about this.
>
> I have a parent table which holds a list of days.
> The child table holds a list of cities and a key
> from the parent table.
>
> It?s for a travel agent. On any particular day, the
> agent can have
> several tours running. Each tour goes to one city.
> I?d like to get my
> output as a two field recordset. The first field
> holding the day and
> the second field as a comma delimited list of the
> city names from the
> child table.
>
> I think I need a nested SELECT, but I?m not sure
> exactly how to form it.
> Can someone point me in the right direction?
>
> Thx
>
> Phil
>
>
>
>
>
> aspmailbox@y...
> $subst('Email.Unsub')
>
> Read the future with ebooks at B&N
>
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid=rn_ebooks
__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
Message #3 by "Philip Sayers" <philipsayers@m...> on Wed, 28 Nov 2001 18:07:06 -0500
|
|
I managed to get that far. But I'd like to concatenate the results of
the subquery into a single field. So my resulting recordset turns out
as
Date, cityname, cityname, cityname
Next Date, cityname, cityname, cityname
Rather than
Date, cityname1
Date, cityname2
Date, cityname3
Next Date, cityname4
Next Date, cityname5
Next Date, cityname6
-----Original Message-----
From: dont worry [mailto:aspmailbox@y...]
Sent: Wednesday, November 28, 2001 5:08 PM
To: ASP Databases
Subject: [asp_databases] Re: quickSQL question.
Select cities
From ToursTbl
Where Day IN
(Select day from DaysTbl where day = thisdayplease)
Its a subquery, hope it helps.
DW
--- Philip Sayers <philipsayers@m...> wrote:
> I?m trying to format a recordset to send to my
> webpage. I?m not sure
> how to go about this.
>
> I have a parent table which holds a list of days.
> The child table holds a list of cities and a key
> from the parent table.
>
> It?s for a travel agent. On any particular day, the
> agent can have
> several tours running. Each tour goes to one city.
> I?d like to get my
> output as a two field recordset. The first field
> holding the day and
> the second field as a comma delimited list of the
> city names from the
> child table.
>
> I think I need a nested SELECT, but I?m not sure
> exactly how to form it.
> Can someone point me in the right direction?
>
> Thx
>
> Phil
>
>
>
>
>
> aspmailbox@y...
> $subst('Email.Unsub')
>
> Read the future with ebooks at B&N
>
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego
ryid=rn_ebooks
__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
philipsayers@m...
$subst('Email.Unsub')
Read the future with ebooks at B&N
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego
ryid=rn_ebooks
Message #4 by David Cameron <dcameron@i...> on Thu, 29 Nov 2001 10:32:56 +1100
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C17865.021680C6
Content-Type: text/plain
Mmm, nasty one.
This can be using CASE for SQL Server or IFF for Access.
Example from BOL (SQL Server):
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
have a look under pivot table, and crosstab reports.
There would be problems with this one if the query needed to be scaleable
(ie the dates change, either through additions, editing or things being
deleted) as I suspect it does. The you would have to use something to
generate the query.
It might be less resource intensive to loop through you recordset of dates,
open up a new recordset for each date and use GetString to generate the
comma separated city string.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Philip Sayers [mailto:philipsayers@m...]
Sent: Thursday, 29 November 2001 9:07 AM
To: ASP Databases
Subject: [asp_databases] Re: quickSQL question.
I managed to get that far. But I'd like to concatenate the results of
the subquery into a single field. So my resulting recordset turns out
as
Date, cityname, cityname, cityname
Next Date, cityname, cityname, cityname
Rather than
Date, cityname1
Date, cityname2
Date, cityname3
Next Date, cityname4
Next Date, cityname5
Next Date, cityname6
-----Original Message-----
From: dont worry [mailto:aspmailbox@y...]
Sent: Wednesday, November 28, 2001 5:08 PM
To: ASP Databases
Subject: [asp_databases] Re: quickSQL question.
Select cities
From ToursTbl
Where Day IN
(Select day from DaysTbl where day = thisdayplease)
Its a subquery, hope it helps.
DW
--- Philip Sayers <philipsayers@m...> wrote:
> I'm trying to format a recordset to send to my
> webpage. I'm not sure
> how to go about this.
>
> I have a parent table which holds a list of days.
> The child table holds a list of cities and a key
> from the parent table.
>
> It's for a travel agent. On any particular day, the
> agent can have
> several tours running. Each tour goes to one city.
> I'd like to get my
> output as a two field recordset. The first field
> holding the day and
> the second field as a comma delimited list of the
> city names from the
> child table.
>
> I think I need a nested SELECT, but I'm not sure
> exactly how to form it.
> Can someone point me in the right direction?
>
> Thx
>
> Phil
>
>
>
>
>
> aspmailbox@y...
> $subst('Email.Unsub')
>
> Read the future with ebooks at B&N
>
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego
ryid=rn_ebooks
__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
philipsayers@m...
$subst('Email.Unsub')
Read the future with ebooks at B&N
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego
ryid=rn_ebooks
$subst('Email.Unsub')
Read the future with ebooks at B&N
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid
=rn_ebooks
Message #5 by dont worry <aspmailbox@y...> on Wed, 28 Nov 2001 16:02:19 -0800 (PST)
|
|
You might try the group by sql feature, but you will
probably use a loop within a loop. Something like,
do while date = 1stdate
loop thru cities
next date
DW
--- Philip Sayers <philipsayers@m...> wrote:
> I managed to get that far. But I'd like to
> concatenate the results of
> the subquery into a single field. So my resulting
> recordset turns out
> as
>
>
> Date, cityname, cityname, cityname
> Next Date, cityname, cityname, cityname
>
> Rather than
> Date, cityname1
> Date, cityname2
> Date, cityname3
> Next Date, cityname4
> Next Date, cityname5
> Next Date, cityname6
>
> -----Original Message-----
> From: dont worry [mailto:aspmailbox@y...]
> Sent: Wednesday, November 28, 2001 5:08 PM
> To: ASP Databases
> Subject: [asp_databases] Re: quickSQL question.
>
> Select cities
> From ToursTbl
> Where Day IN
> (Select day from DaysTbl where day = thisdayplease)
>
> Its a subquery, hope it helps.
> DW
>
>
> --- Philip Sayers <philipsayers@m...> wrote:
> > I?m trying to format a recordset to send to my
> > webpage. I?m not sure
> > how to go about this.
> >
> > I have a parent table which holds a list of days.
> > The child table holds a list of cities and a key
> > from the parent table.
> >
> > It?s for a travel agent. On any particular day,
> the
> > agent can have
> > several tours running. Each tour goes to one
> city.
> > I?d like to get my
> > output as a two field recordset. The first field
> > holding the day and
> > the second field as a comma delimited list of the
> > city names from the
> > child table.
> >
> > I think I need a nested SELECT, but I?m not sure
> > exactly how to form it.
> > Can someone point me in the right direction?
> >
> > Thx
> >
> > Phil
> >
> >
> >
> >
> >
> > aspmailbox@y...
> > $subst('Email.Unsub')
> >
> > Read the future with ebooks at B&N
> >
>
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego
> ryid=rn_ebooks
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! GeoCities - quick and easy web site hosting,
> just $8.95/month.
> http://geocities.yahoo.com/ps/info1
>
>
> philipsayers@m...
> $subst('Email.Unsub')
>
> Read the future with ebooks at B&N
>
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego
> ryid=rn_ebooks
>
>
>
>
> aspmailbox@y...
> $subst('Email.Unsub')
>
> Read the future with ebooks at B&N
>
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid=rn_ebooks
__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
|
|
|
View Cart
