Can sql queries be nested in sql queries?

I am having problems with a template that uses the information from one sql
query to generate a query on another table.  Let me be more specific.  The
template in question receives a category ID value from a web page that
provides a menu of several categories to choose from.  Each Category ID
represents several Items or products to choose from.  When the template
receives the category ID, the first thing it does is run a query on my
item_category table (this table lists all of the items and associates them
with the appropriate category).  This query returns several itemIDs that are
all associated with the provided CategoryID.  Once this is done, I want to
display more data about each itemID.  This data is in a different table (The
Item table holds the name and description etc...).  I've nested a second
query in the " while (sql.MoveNext) " loop that returns the itemIDs from the
first query, however this second query returns null.  I know that there's a
simple solution to this problem, but I'm at a loss as to what it is.  Does
anyone know how to solve my problem?  Any help would be greatly appreciated.
I've posted the actual code I've written below...

Thanks,

Scott

<!--SCRIPT
    var idQuery = connection.CreateQuery("select itemid from item_categories
where category =" + document.value.catid)
    document.writeln("<TABLE BORDER=1>")
    while (idQuery.MoveNext()) {
        id = idQuery.GetValue(1);
        var itemQuery = connection.CreateQuery("select name from item where
id = " + id);
        document.writeln("<TR><TD><A HREF=itempage.stm?itemid=" + id +
"&orderid=" + orderid + ">" + itemQuery.GetValue(1) + "</A></TD></TR>")
    }
    document.writeln("</TABLE>")
-->


0
scott
6/19/1998 9:25:32 PM
sybase.powerdynamo 591 articles. 0 followers. Follow

9 Replies
1714 Views

Similar Articles

[PageSpeed] 58
Get it on Google Play
Get it on Apple App Store

Scott,

You're on row zero of your cursor for the second query.  MoveFirst() or
MoveNext() to get to an actual row of the result set.

HTH,

Carson

----------------------------------------------------------------------------
Carson Hager                     Dynamic Data Solutions
Team Powersoft                 http://www.dyn-data.com
chager@dyn-data.com


            ********  Are You Ready for the Web  ********
                             www.jumpstart98.com


scott W wrote in message ...
>I am having problems with a template that uses the information from one sql
>query to generate a query on another table.  Let me be more specific.  The
>template in question receives a category ID value from a web page that
>provides a menu of several categories to choose from.  Each Category ID
>represents several Items or products to choose from.  When the template
>receives the category ID, the first thing it does is run a query on my
>item_category table (this table lists all of the items and associates them
>with the appropriate category).  This query returns several itemIDs that
are
>all associated with the provided CategoryID.  Once this is done, I want to
>display more data about each itemID.  This data is in a different table
(The
>Item table holds the name and description etc...).  I've nested a second
>query in the " while (sql.MoveNext) " loop that returns the itemIDs from
the
>first query, however this second query returns null.  I know that there's a
>simple solution to this problem, but I'm at a loss as to what it is.  Does
>anyone know how to solve my problem?  Any help would be greatly
appreciated.
>I've posted the actual code I've written below...
>
>Thanks,
>
>Scott
>
><!--SCRIPT
>    var idQuery = connection.CreateQuery("select itemid from
item_categories
>where category =" + document.value.catid)
>    document.writeln("<TABLE BORDER=1>")
>    while (idQuery.MoveNext()) {
>        id = idQuery.GetValue(1);
>        var itemQuery = connection.CreateQuery("select name from item where
>id = " + id);
>        document.writeln("<TR><TD><A HREF=itempage.stm?itemid=" + id +
>"&orderid=" + orderid + ">" + itemQuery.GetValue(1) + "</A></TD></TR>")
>    }
>    document.writeln("</TABLE>")
>-->
>
>


0
Carson
6/20/1998 3:52:04 AM
Thanks Carson,

I had a feeling I was making a pretty silly mistake.  Here's another
question for you.  While I was trying to figure this thing out on my own, I
brushed up on my sql a bit, and figured out how to nest a subquery in my
original query to give me the solution I needed.  Here's the code:

var idQuery = connection.CreateQuery("select id, name, customtext1 from item
where id = (select itemid from item_categories where item.id =
item_categories.itemid and category =" + document.value.catid + ")")
    rowcount = idQuery.GetRowCount()
    for (i=0; i < rowcount; i++) {
        idQuery.MoveNext();
        id = idQuery.GetValue(1)
        name = idQuery.GetValue(2)
        cust1 = idQuery.GetValue(3)

Here's my question.  Which approach is better.  I get the feeling that the
code above has a pretty high overhead since the query takes about 3 times
longer to run.  Would you recommend the above code or my original code (with
the MoveNext() line added of course).  Thanks for your help.  I really
appreciate any advice you can offer.

Thanks,  Scott

Carson Hager[TeamPS] wrote in message
<6H98cX$m9GA.79@forums.powersoft.com>...
>Scott,
>
>You're on row zero of your cursor for the second query.  MoveFirst() or
>MoveNext() to get to an actual row of the result set.
>
>HTH,
>
>Carson
>
>---------------------------------------------------------------------------
-
>Carson Hager                     Dynamic Data Solutions
>Team Powersoft                 http://www.dyn-data.com
>chager@dyn-data.com
>
>
>            ********  Are You Ready for the Web  ********
>                             www.jumpstart98.com
>
>



0
scott
6/20/1998 7:23:29 PM
This is a multi-part message in MIME format.
--------------AD161566F877BC82AB38B3FB
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

One thing that is faster is this.  Dont do:

rowcount = query.GetRowCount();
for(i=0;i<rowcount;i++)

This AFAIK requires the query object to move through the whole result set, what
I prefer to do is this:

while(query.MoveNext())

This solves two things.  One, you get the first MoveNext() executed, and
secondly you dont need to do the GetRowCount() as MoveNext() will return false
when there are no more rows to read.  This code is also more portable to those
RDBMS systems which dont support dynamic scrollable cursors, like ASE.

Dave Wolf
Sybase Professional Services

scott W wrote:

> Thanks Carson,
>
> I had a feeling I was making a pretty silly mistake.  Here's another
> question for you.  While I was trying to figure this thing out on my own, I
> brushed up on my sql a bit, and figured out how to nest a subquery in my
> original query to give me the solution I needed.  Here's the code:
>
> var idQuery = connection.CreateQuery("select id, name, customtext1 from item
> where id = (select itemid from item_categories where item.id =
> item_categories.itemid and category =" + document.value.catid + ")")
>     rowcount = idQuery.GetRowCount()
>     for (i=0; i < rowcount; i++) {
>         idQuery.MoveNext();
>         id = idQuery.GetValue(1)
>         name = idQuery.GetValue(2)
>         cust1 = idQuery.GetValue(3)
>
> Here's my question.  Which approach is better.  I get the feeling that the
> code above has a pretty high overhead since the query takes about 3 times
> longer to run.  Would you recommend the above code or my original code (with
> the MoveNext() line added of course).  Thanks for your help.  I really
> appreciate any advice you can offer.
>
> Thanks,  Scott
>
> Carson Hager[TeamPS] wrote in message
> <6H98cX$m9GA.79@forums.powersoft.com>...
> >Scott,
> >
> >You're on row zero of your cursor for the second query.  MoveFirst() or
> >MoveNext() to get to an actual row of the result set.
> >
> >HTH,
> >
> >Carson
> >
> >---------------------------------------------------------------------------
> -
> >Carson Hager                     Dynamic Data Solutions
> >Team Powersoft                 http://www.dyn-data.com
> >chager@dyn-data.com
> >
> >
> >            ********  Are You Ready for the Web  ********
> >                             www.jumpstart98.com
> >
> >



--------------AD161566F877BC82AB38B3FB
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for David Wolf
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             David Wolf
n:              Wolf;David
org:            Sybase Professional Services
adr:            6550 Rock Spring Dr;;Suite 800;Bethesda;MD;20817;USA
email;internet: dwolf@sybase.com
title:          Consultant
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard


--------------AD161566F877BC82AB38B3FB--

0
David
6/20/1998 9:21:15 PM
Thanks Dave.  I appreciate the good advice.  Eventually, we will move to
ASE, and you just saved me god knows how much time when we do that.

Take care!

Scott


David Lance Wolf wrote in message <358C27CB.DAA88E4F@sybase.com>...
>One thing that is faster is this.  Dont do:
>
>rowcount = query.GetRowCount();
>for(i=0;i<rowcount;i++)
>
>This AFAIK requires the query object to move through the whole result set,
what
>I prefer to do is this:
>
>while(query.MoveNext())
>
>This solves two things.  One, you get the first MoveNext() executed, and
>secondly you dont need to do the GetRowCount() as MoveNext() will return
false
>when there are no more rows to read.  This code is also more portable to
those
>RDBMS systems which dont support dynamic scrollable cursors, like ASE.
>
>Dave Wolf
>Sybase Professional Services
>



0
scott
6/20/1998 10:53:51 PM
Scott,

I see that Dave helped you out on your original question.  I'd like to
propose to you a faster approach to your SQL.

Instead of using a subselect, try the following SQL for your query:

var idQuery = connection.CreateQuery("select id, name, customtext1 from
item, item_categories where item.id = item_categories.itemid and
item_categories.category =" + document.value.catid )

Correlated subselects are very expensive.  They cause an individual index
lookup for each row where the inner join builds the entire result set for
you with one lookup.

HTH,

Carson

----------------------------------------------------------------------------
Carson Hager                     Dynamic Data Solutions
Team Powersoft                 http://www.dyn-data.com
chager@dyn-data.com


            ********  Are You Ready for the Web  ********
                             www.jumpstart98.com


scott W wrote in message ...
>Thanks Carson,
>
>I had a feeling I was making a pretty silly mistake.  Here's another
>question for you.  While I was trying to figure this thing out on my own, I
>brushed up on my sql a bit, and figured out how to nest a subquery in my
>original query to give me the solution I needed.  Here's the code:
>
>var idQuery = connection.CreateQuery("select id, name, customtext1 from
item
>where id = (select itemid from item_categories where item.id =
>item_categories.itemid and category =" + document.value.catid + ")")
>    rowcount = idQuery.GetRowCount()
>    for (i=0; i < rowcount; i++) {
>        idQuery.MoveNext();
>        id = idQuery.GetValue(1)
>        name = idQuery.GetValue(2)
>        cust1 = idQuery.GetValue(3)
>
>Here's my question.  Which approach is better.  I get the feeling that the
>code above has a pretty high overhead since the query takes about 3 times
>longer to run.  Would you recommend the above code or my original code
(with
>the MoveNext() line added of course).  Thanks for your help.  I really
>appreciate any advice you can offer.
>
>Thanks,  Scott
>
>Carson Hager[TeamPS] wrote in message
><6H98cX$m9GA.79@forums.powersoft.com>...
>>Scott,
>>
>>You're on row zero of your cursor for the second query.  MoveFirst() or
>>MoveNext() to get to an actual row of the result set.
>>
>>HTH,
>>
>>Carson
>>
>>--------------------------------------------------------------------------
-
>-
>>Carson Hager                     Dynamic Data Solutions
>>Team Powersoft                 http://www.dyn-data.com
>>chager@dyn-data.com
>>
>>
>>            ********  Are You Ready for the Web  ********
>>                             www.jumpstart98.com
>>
>>
>
>
>


0
Carson
6/21/1998 12:14:29 AM
This is a multi-part message in MIME format.
--------------9210A7214A1149FA6407C684
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Heh, dont wanna let this thread die....

Some optimizers, ASE, and AFAIK ASA now, will flatten correlated subqueries into
joins to alleviate this performance hit of the virtual index on the sub-select.
Again, now 100% sure about ASA, but I know ASE does this.  But try it both ways,
P&T is an art, not a science, as your learning, try it, change one variable, try
it, change one variable.......

Dave Wolf
Sybase Professional Services

Carson Hager[TeamPS] wrote:

> Scott,
>
> I see that Dave helped you out on your original question.  I'd like to
> propose to you a faster approach to your SQL.
>
> Instead of using a subselect, try the following SQL for your query:
>
> var idQuery = connection.CreateQuery("select id, name, customtext1 from
> item, item_categories where item.id = item_categories.itemid and
> item_categories.category =" + document.value.catid )
>
> Correlated subselects are very expensive.  They cause an individual index
> lookup for each row where the inner join builds the entire result set for
> you with one lookup.
>
> HTH,
>
> Carson
>
> ----------------------------------------------------------------------------
> Carson Hager                     Dynamic Data Solutions
> Team Powersoft                 http://www.dyn-data.com
> chager@dyn-data.com
>
>             ********  Are You Ready for the Web  ********
>                              www.jumpstart98.com
>
> scott W wrote in message ...
> >Thanks Carson,
> >
> >I had a feeling I was making a pretty silly mistake.  Here's another
> >question for you.  While I was trying to figure this thing out on my own, I
> >brushed up on my sql a bit, and figured out how to nest a subquery in my
> >original query to give me the solution I needed.  Here's the code:
> >
> >var idQuery = connection.CreateQuery("select id, name, customtext1 from
> item
> >where id = (select itemid from item_categories where item.id =
> >item_categories.itemid and category =" + document.value.catid + ")")
> >    rowcount = idQuery.GetRowCount()
> >    for (i=0; i < rowcount; i++) {
> >        idQuery.MoveNext();
> >        id = idQuery.GetValue(1)
> >        name = idQuery.GetValue(2)
> >        cust1 = idQuery.GetValue(3)
> >
> >Here's my question.  Which approach is better.  I get the feeling that the
> >code above has a pretty high overhead since the query takes about 3 times
> >longer to run.  Would you recommend the above code or my original code
> (with
> >the MoveNext() line added of course).  Thanks for your help.  I really
> >appreciate any advice you can offer.
> >
> >Thanks,  Scott
> >
> >Carson Hager[TeamPS] wrote in message
> ><6H98cX$m9GA.79@forums.powersoft.com>...
> >>Scott,
> >>
> >>You're on row zero of your cursor for the second query.  MoveFirst() or
> >>MoveNext() to get to an actual row of the result set.
> >>
> >>HTH,
> >>
> >>Carson
> >>
> >>--------------------------------------------------------------------------
> -
> >-
> >>Carson Hager                     Dynamic Data Solutions
> >>Team Powersoft                 http://www.dyn-data.com
> >>chager@dyn-data.com
> >>
> >>
> >>            ********  Are You Ready for the Web  ********
> >>                             www.jumpstart98.com
> >>
> >>
> >
> >
> >



--------------9210A7214A1149FA6407C684
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for David Wolf
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             David Wolf
n:              Wolf;David
org:            Sybase Professional Services
adr:            6550 Rock Spring Dr;;Suite 800;Bethesda;MD;20817;USA
email;internet: dwolf@sybase.com
title:          Consultant
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard


--------------9210A7214A1149FA6407C684--

0
David
6/22/1998 2:45:39 AM
I wasn't aware that was the case.  Did you hear this regarding the 5.5
version or 6.0?

Carson

----------------------------------------------------------------------------
Carson Hager                     Dynamic Data Solutions
Team Powersoft                 http://www.dyn-data.com
chager@dyn-data.com


            ********  Are You Ready for the Web  ********
                             www.jumpstart98.com


David Lance Wolf wrote in message <358DC553.7DFE9655@sybase.com>...
>Heh, dont wanna let this thread die....
>
>Some optimizers, ASE, and AFAIK ASA now, will flatten correlated subqueries
into
>joins to alleviate this performance hit of the virtual index on the
sub-select.
>Again, now 100% sure about ASA, but I know ASE does this.  But try it both
ways,
>P&T is an art, not a science, as your learning, try it, change one
variable, try
>it, change one variable.......
>
>Dave Wolf
>Sybase Professional Services
>
>Carson Hager[TeamPS] wrote:
>
>> Scott,
>>
>> I see that Dave helped you out on your original question.  I'd like to
>> propose to you a faster approach to your SQL.
>>
>> Instead of using a subselect, try the following SQL for your query:
>>
>> var idQuery = connection.CreateQuery("select id, name, customtext1 from
>> item, item_categories where item.id = item_categories.itemid and
>> item_categories.category =" + document.value.catid )
>>
>> Correlated subselects are very expensive.  They cause an individual index
>> lookup for each row where the inner join builds the entire result set for
>> you with one lookup.
>>
>> HTH,
>>
>> Carson
>>
>> -------------------------------------------------------------------------
---
>> Carson Hager                     Dynamic Data Solutions
>> Team Powersoft                 http://www.dyn-data.com
>> chager@dyn-data.com
>>
>>             ********  Are You Ready for the Web  ********
>>                              www.jumpstart98.com
>>
>> scott W wrote in message ...
>> >Thanks Carson,
>> >
>> >I had a feeling I was making a pretty silly mistake.  Here's another
>> >question for you.  While I was trying to figure this thing out on my
own, I
>> >brushed up on my sql a bit, and figured out how to nest a subquery in my
>> >original query to give me the solution I needed.  Here's the code:
>> >
>> >var idQuery = connection.CreateQuery("select id, name, customtext1 from
>> item
>> >where id = (select itemid from item_categories where item.id =
>> >item_categories.itemid and category =" + document.value.catid + ")")
>> >    rowcount = idQuery.GetRowCount()
>> >    for (i=0; i < rowcount; i++) {
>> >        idQuery.MoveNext();
>> >        id = idQuery.GetValue(1)
>> >        name = idQuery.GetValue(2)
>> >        cust1 = idQuery.GetValue(3)
>> >
>> >Here's my question.  Which approach is better.  I get the feeling that
the
>> >code above has a pretty high overhead since the query takes about 3
times
>> >longer to run.  Would you recommend the above code or my original code
>> (with
>> >the MoveNext() line added of course).  Thanks for your help.  I really
>> >appreciate any advice you can offer.
>> >
>> >Thanks,  Scott
>> >
>> >Carson Hager[TeamPS] wrote in message
>> ><6H98cX$m9GA.79@forums.powersoft.com>...
>> >>Scott,
>> >>
>> >>You're on row zero of your cursor for the second query.  MoveFirst() or
>> >>MoveNext() to get to an actual row of the result set.
>> >>
>> >>HTH,
>> >>
>> >>Carson
>> >>
>>
>>--------------------------------------------------------------------------
>> -
>> >-
>> >>Carson Hager                     Dynamic Data Solutions
>> >>Team Powersoft                 http://www.dyn-data.com
>> >>chager@dyn-data.com
>> >>
>> >>
>> >>            ********  Are You Ready for the Web  ********
>> >>                             www.jumpstart98.com
>> >>
>> >>
>> >
>> >
>> >
>
>
>


0
Carson
6/22/1998 3:05:08 AM
Thanks Dave & Carson!

I've been tinkering with a few different ways to tune my sql, and itlooks
like your advice takes the cake.  I've combined your suggested sql with
Dave's suggestion to use while itemQuery.MoveNext(), and the query is
running just as fast as can be.  I owe both of you much thanks.

Thanks guys...

Scott


Carson Hager[TeamPS] wrote in message ...
>Scott,
>
>I see that Dave helped you out on your original question.  I'd like to
>propose to you a faster approach to your SQL.
>
>Instead of using a subselect, try the following SQL for your query:
>
>var idQuery = connection.CreateQuery("select id, name, customtext1 from
>item, item_categories where item.id = item_categories.itemid and
>item_categories.category =" + document.value.catid )
>
>Correlated subselects are very expensive.  They cause an individual index
>lookup for each row where the inner join builds the entire result set for
>you with one lookup.
>
>HTH,
>
>Carson
>
>---------------------------------------------------------------------------
-
>Carson Hager                     Dynamic Data Solutions
>Team Powersoft                 http://www.dyn-data.com
>chager@dyn-data.com
>
>
>            ********  Are You Ready for the Web  ********
>                             www.jumpstart98.com
>
>



0
scott
6/22/1998 6:32:10 AM
This is your confirmation that ASA (SQL Anywhere) also converts the subquery
into a join.

In fact any query given to the SQL Anywhere engine is immediately
re-written.  Depending on what you are asking for, most of the time the
engine processes a query that is different than supplied.

--
David Fishburn
Sybase
Please post only to the news group.

Carson Hager[TeamPS] wrote in message ...
>I wasn't aware that was the case.  Did you hear this regarding the 5.5
>version or 6.0?
>
>Carson
>
>---------------------------------------------------------------------------
-
>Carson Hager                     Dynamic Data Solutions
>Team Powersoft                 http://www.dyn-data.com
>chager@dyn-data.com
>
>
>            ********  Are You Ready for the Web  ********
>                             www.jumpstart98.com
>
>
>David Lance Wolf wrote in message <358DC553.7DFE9655@sybase.com>...
>>Heh, dont wanna let this thread die....
>>
>>Some optimizers, ASE, and AFAIK ASA now, will flatten correlated
subqueries
>into
>>joins to alleviate this performance hit of the virtual index on the
>sub-select.
>>Again, now 100% sure about ASA, but I know ASE does this.  But try it both
>ways,
>>P&T is an art, not a science, as your learning, try it, change one
>variable, try
>>it, change one variable.......
>>
>>Dave Wolf
>>Sybase Professional Services
>>
>>Carson Hager[TeamPS] wrote:
>>
>>> Scott,
>>>
>>> I see that Dave helped you out on your original question.  I'd like to
>>> propose to you a faster approach to your SQL.
>>>
>>> Instead of using a subselect, try the following SQL for your query:
>>>
>>> var idQuery = connection.CreateQuery("select id, name, customtext1 from
>>> item, item_categories where item.id = item_categories.itemid and
>>> item_categories.category =" + document.value.catid )
>>>
>>> Correlated subselects are very expensive.  They cause an individual
index
>>> lookup for each row where the inner join builds the entire result set
for
>>> you with one lookup.
>>>
>>> HTH,
>>>
>>> Carson
>>>
>>> ------------------------------------------------------------------------
-
>---
>>> Carson Hager                     Dynamic Data Solutions
>>> Team Powersoft                 http://www.dyn-data.com
>>> chager@dyn-data.com
>>>
>>>             ********  Are You Ready for the Web  ********
>>>                              www.jumpstart98.com
>>>
>>> scott W wrote in message ...
>>> >Thanks Carson,
>>> >
>>> >I had a feeling I was making a pretty silly mistake.  Here's another
>>> >question for you.  While I was trying to figure this thing out on my
>own, I
>>> >brushed up on my sql a bit, and figured out how to nest a subquery in
my
>>> >original query to give me the solution I needed.  Here's the code:
>>> >
>>> >var idQuery = connection.CreateQuery("select id, name, customtext1 from
>>> item
>>> >where id = (select itemid from item_categories where item.id =
>>> >item_categories.itemid and category =" + document.value.catid + ")")
>>> >    rowcount = idQuery.GetRowCount()
>>> >    for (i=0; i < rowcount; i++) {
>>> >        idQuery.MoveNext();
>>> >        id = idQuery.GetValue(1)
>>> >        name = idQuery.GetValue(2)
>>> >        cust1 = idQuery.GetValue(3)
>>> >
>>> >Here's my question.  Which approach is better.  I get the feeling that
>the
>>> >code above has a pretty high overhead since the query takes about 3
>times
>>> >longer to run.  Would you recommend the above code or my original code
>>> (with
>>> >the MoveNext() line added of course).  Thanks for your help.  I really
>>> >appreciate any advice you can offer.
>>> >
>>> >Thanks,  Scott
>>> >
>>> >Carson Hager[TeamPS] wrote in message
>>> ><6H98cX$m9GA.79@forums.powersoft.com>...
>>> >>Scott,
>>> >>
>>> >>You're on row zero of your cursor for the second query.  MoveFirst()
or
>>> >>MoveNext() to get to an actual row of the result set.
>>> >>
>>> >>HTH,
>>> >>
>>> >>Carson
>>> >>
>>>
>>>-------------------------------------------------------------------------
-
>>> -
>>> >-
>>> >>Carson Hager                     Dynamic Data Solutions
>>> >>Team Powersoft                 http://www.dyn-data.com
>>> >>chager@dyn-data.com
>>> >>
>>> >>
>>> >>            ********  Are You Ready for the Web  ********
>>> >>                             www.jumpstart98.com
>>> >>
>>> >>
>>> >
>>> >
>>> >
>>
>>
>>
>
>


0
David
6/22/1998 6:22:28 PM
Reply:

Similar Artilces:

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

can i do an sql query then use the result of that in another query...
for instance i need to find value of storyId from one table then make sure i do not pull that record from another table. is this possible With SQL you can do something like this: select * from TableB where StoryIdValue not in (select StoryIdValue from TableA) This is just 1 of many possible solutions. ChrisDont forget to click "Mark as Answer" on the post that helped you.This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. Yes this is possible. This is what is called a "Nested Query" in SQL. ...

how can i do response.write sql to see sql query(values)
In regular asp i used to do a lot of response.write sql to see my query, for debugging purposes. Can you please tell me how i can do the same for the following code, i used here the dataset. ****************Code*********** Dim UpdateCmd As String = "UPDATE tbl_labels SET eng_lbl = @engl, " & _ "esp_lbl = @espn, fr_lbl = @fren, ger_lbl = @german where id = @Id" MyCommand = New SqlCommand(UpdateCmd, MyConnection) MyCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.int, 9)) MyCommand.Parameters.Add(New SqlParameter("@engl&qu...

SQL Query , SQL or Procedural
HIHi i need some help on what the SQL query would be, let's say i have a resultant table like this. Group TestID Result A AA 10 A BB 30 A CC 20 B AB 200 B AC 300 A DD 50 C AA 1000 and i need to query by group and re-name every field as its TestID value for that selected group:  Example:if group "A" is selected: AA BB CC DD 10 30 20 50 if group "B" is selected: AB BC 200 300 and so on.. thanks.   Here is a sample for you to play with PIVOT: SELECT [...

Can't find nested SQL Query
Can someone please tell me how to refrence the Details2SqlDataSource1 in codebehind so I can do: Details2SqlDataSource1.InsertParameters("VendorCode").DefaultValue = Request.QueryString("VendorCode") Here's my code:  <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="test.aspx.vb" Inherits="test" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <table style="w...

Nested SQL Queries
Hey guys, I'm new to DB programming in .net and am having trouble with trying to display an catalouge on a webpage.I have created the following table: category{id, title, level, parent} id - identifiertitle - text valuelevel - the level of the catalouge entryparent - the parent of this categoryIn classic asp i would have neested a query e.g. loop all db entries where level = 0      current = rs(id).value      loop all entries where parent = current            displ...

SQL
Apologies for asking such a simple question, but here goes. I have two tables, for the sake of argument call them a and b . create table a (id numeric, checkdate datetime) create table b (id numeric, a_id numeric, checkdate datetime) I want to update the a table with the most recent checkdate of any related b record. In Oracle, this would be update a ta set checkdate = (select max(checkdate) from b tb where tb.a_id = ta.id) When I try to run this in Sybase, I get: The column prefix 'ta' does not match with a table name or alias name used in the query. Either th...

Nest SQL queries
Hi,I have the followind code, which selects product categories and writes them to a <div> then for each product category, it also gets the associated products and writes them to a list inside the relevant div, so my output would be something like: ProductCategory number 1First product in category 1Second product in category 1Third product in category 1Product Category number 2First product in category 2Second product in category 2etc etcThe code is like this:        SqlCommand comm_productcategory = new SqlCommand("SELECT productcategory_id,...

Huge SQL Queries in SQL 2005
Good evening:  We're porting an old app written in ASP.NET  1.1433 and SQL 2000 to ASP.NET 2.0 and SQL 2005.  In the old app we have a few data grids that are populated from a dataset pulled from the database.  We use a SQL query that we build based on more than 10 different user inputs, the result of which is an enormously complicated SQL string.  We'd like to move this processing into a SPROC in the 2005 database. Rather than writing stored procedures to create the SQL SELECT statement, is it possible to pass an entire select state ment to a SPROC and hav...

Sql Query is dropping characters in my query!
Hey, I'm querying a database like this:   Dim strSql As String Dim email As String  strSql = String.Format("select * from tblsales where email={0}", email)   The string 'email' is an email address, for this example, lets use 'bobmarley@yahoo.co.uk'What happens is that somewhere its cutting off my email address at the end, in some cases it takes away the full '.co.uk' and leaves you with 'bobmarley@yahoo'and in other cases takes the '.uk' away but keeps the '.co'Anyone got any ideas? The valu...

access query to custom sql query in asp
 I have a query that works well in access, i go to the query builder in ms access and in the criteria field type this : Like "*" & [Enter] & "*" the output is what i need exactly, now in accessdatasource i select a custom sql query and using that same ms access db i try to add the query and it doesnt work, what am i doing wrong?i have a text field that when entered shows me the output in a gridview, so i am configuring the datasource to show this query and then attach the gridview with the datasource.basically i just need a custom sql query equivalent to...

LDAP queries linked to SQL database queries
Hi, I am wondering whether it is common practice to use the AD domain users information as a link table for user information in database queries. My first thought is that it would be too slow since it can potentially query the AD many times depending on the number of records returned. Any insight is appreciated, sivilianMy Weblogs...

how to connect sql server using sql query?
Hi all, Please explain how to connect sql server using sql query?. My problem is need to get the data from one server table and insert into another server tables using stored procedure. It is possible by using C# coding. But i need to do using stored procedure. becoz each server have own connection string, username and password.  Please anyone help me? Thanks in advance!!! Hi, You can create a linked server between the two databases and then fire the queries. http://www.sqlservercurry.com/2008/03/how-to-set-up-your-database-for.html   HTH,Suprotim AgarwalA...

Nested Loop in SQL Query
Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit          Day1         Day2           Day3        Day4          Day5Name1     25             45       &n...

Web resources about - Can sql queries be nested in sql queries? - sybase.powerdynamo

The Queries - Wikipedia, the free encyclopedia
In science , The Queries or simply "Queries" is the third book to English physicist Isaac Newton 's Opticks , with various numbers of Query sections ...

Publishers Can See What Graph Search Queries Led To Their Page
It seems Facebook is tracking Graph Search queries, allowing publishers to see what words typed into the search bar led to their website. Sister ...

Facebook adds search history to Activity Log so users can delete queries
Facebook today announced that it will start including search history in users’ Activity Logs. The feature will be rolled out to all users in ...

faroo_p2p: Six month after launch the FAROO Search API http://t.co/RQlyOBZC serves 24 million queries/month ...
faroo_p2p: Six month after launch the FAROO Search API http://t.co/RQlyOBZC serves 24 million queries/month. A tenfold increase in the last month. ...

App Store - IDdx: Infectious Disease Queries
Get IDdx: Infectious Disease Queries on the App Store. See screenshots and ratings, and read customer reviews.

Michael Jackson queries - Google - Flickr - Photo Sharing!
Explore Search Engine Land's photos on Flickr. Search Engine Land has uploaded 1714 photos to Flickr.

'Safari UniBar' Brings Searches and Web Queries to One Unified Bar - YouTube
Retweet: http://clicktotweet.com/27wfR Name: Safari UniBar Description: Combined web queries and search from one bar, just like Google Chrome. ...

Gold Coast queries AFL's values as academy stoush intensifies
Gold Coast chairman John Witheriff&#10003; has questioned the AFL&#8217;s business values and accused it of a lack of courtesy in the decision ...

Malcolm Turnbull queries Foxtel's free content
Communications Minister Malcolm Turnbull is preparing to rip up a controversial clause in the retransmission regime in a move that could force ...

Firefox 14 protects search queries from 'bad guys,' not advertisers
Firefox 14 protects search queries from 'bad guys,' not advertisers Australian Macworld However, search engine guru Danny Sullivan from Search ...

Resources last updated: 12/19/2015 3:43:02 PM