I have a querie to a database  with a table called brandcat which contains a

key referring to the combination of brands and categories the products can

have.

Now i want to update my product .

while updating I only have the name of category and brand and i have to

select the unique id ( called brandcatid) for this combination.

I used the following sql statement:

***********************

select brandcatid from brandcat where brandid=select brandid from brand

where brandname=Nokiaand catcode=select catcode from categories where

catname=Accessoires

*****************

As you see the sql statement recognised the brandname and the categorie ,

but that is not exactly what i needed... in stead i need the catcode and the

brandid as a result of a select query..

what am i doing wrong?

i insert the source code of my select statement here..

*¨*****************

SQL1=("select brandcatid from brandcat where brandid="&("select brandid from

brand where brandname="&strbrand)&"and catcode="&("select catcode from

categories where catname="&strcat))

***************

Eric,



If I understand your post correctly, it sound as if you need to carry out

a

correlated/repeating subquery - sounds nasty, but basically you just need

to use the 'WHERE .... IN ...' construct.



So, for your query :



  select brandcatid from brandcat where brandid=select brandid from brand

  where brandname=Nokiaand catcode=select catcode from categories where

  catname=Accessoires



you could try :



  ... select brandcatid from brandcat 

      where brandid IN (select brandid from brand where brandname=Nokia)



Something along those lines should give you what you want.



HTH.



Ray Murphy

Goldseal Computer Consultants

www.goldsealdata.com

out of interest can you place an SQL query in an SQL query if you targeting

the same table?



I want to try to test for the month and the year of an event when these

values are passed separately!



TIA



p



-----Original Message-----

From: Ray Murphy [mailto:raymondmurphy@c...]

Sent: 15 September 2000 14:20

To: ASP Databases

Subject: [asp_databases] Re: sql queries in a sql query...





Eric,



If I understand your post correctly, it sound as if you need to carry out

a

correlated/repeating subquery - sounds nasty, but basically you just need

to use the 'WHERE .... IN ...' construct.



So, for your query :



  select brandcatid from brandcat where brandid=select brandid from brand

  where brandname=Nokiaand catcode=select catcode from categories where

  catname=Accessoires



you could try :



  ... select brandcatid from brandcat 

      where brandid IN (select brandid from brand where brandname=Nokia)



Something along those lines should give you what you want.



HTH.



Ray Murphy

Goldseal Computer Consultants

www.goldsealdata.com