asked on

how to select from another query

using asp.

I am trying to select from query.

I.e. I have the 1st query which builds part of my list as so :
strsql = "SELECT DISTINCT REFRENCE FROM Products WHERE (productname like '%cdrom%' and unitsinstock > '0')"

So the above select all the fields REFRENCE from the products table that are distint right?

Now I need to use this query in another query i.e.

strsql2 = "SELECT * FROM STRSQL ...... and then somehow i think use inner join or something to select the other details from refrence..

so if my PRODUCTS TABLE WAS ORIGINALY LIKE SO :
AA1 M
AA1 L
AA1 XL
AA2 M
AA2 L
AA3 XL
AA3 L

so the final data will be
AA1 M
AA2 M
AA3 XL

Tks.

yeroket

SELECT DISTINCT REFRENCE ,
(SELECT TOP 1 size FROM Products t2 WHERE REFRENCE = Products .REFRENCE ) AS size
FROM Products

Guy Hengel [angelIII / a3]

or make the query simply like this:

select reference, min(size) size from products group by reference

Dragonlaird

Select Distinct [REFERENCE], [SIZE] From [PRODUCTS] Where [PRODUCTNAME] = '%cdrom%' and [UNITSINSTOCK] > 0 Order By [REFERENCE]

The above would return a list of all REFERENCE values, with all associated SIZE values for each matching item, and group them together based on REFERENCE values. Only those items in stock that match the supplied name would return.

You didn't specify the criteria for selecting only ONE size (is it the smallest available size? all sizes in stock? just the first size listed in the DB? did you mean to only return a single REFERENCE/SIZE value pair in your second list above?), perhaps if you clarified why you only want one REFERENCE/SIZE value pair to be returned, we could construct a complete SQL statement for you.

yeroket

angelIII - min() would bring back the smallest varchar not the first varchar, the way I understood Ricky11 question he wanted the first value, but I may be wrong.

Guy Hengel [angelIII / a3]

>the way I understood Ricky11 question he wanted the first value
depends what you decalre " the first value ", as row ordering is not guaranteed unless you specify a ORDER BY clause

Dragonlaird

Thanks yeroket and angelIII, I think you both managed to prove the point I was making, we don't know exactly how Ricky11 wants his results returned. We should never assume...

Dragonlaird

Oh... another small point, wildcards shouldn't be used when equating a value in a WHERE clause, you should use the LIKE keyword so I've modified my SQL statement below:

Select Distinct [REFERENCE], [SIZE] From [PRODUCTS] Where [PRODUCTNAME] Like '%cdrom%' and [UNITSINSTOCK] > 0 Order By [REFERENCE]

Ricky11

ASKER

Yes first value.

I will check out the code soon.

Tks ppls.

ASKER CERTIFIED SOLUTION

Dragonlaird

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ricky11

ASKER

The question is slightly differnet from the question you mentioned, the title of this question is how to select from another query, becuase thats what i thought i had to do to get the job done, but i guess not.

With regard to using select distcint [refrence] , [size]... that doesn't return a list of unique refrences or lets say first of refrences.. it does however return unique records but not unique refrences...

now both question are quite similar, but i still dont have a solution....

Ricky11

ASKER

I have one way I can solve this :

I can do a query in access as follows :
SELECT PRODUCTS.Refrence, First(PRODUCTS.Size) AS FirstOfSize,FROM PRODUCTS GROUP BY PRODUCTS.Refrence
HAVING (((First(PRODUCTS.ProductName)) Like "*xx*"));

That gives me the exact results i wanted. Then In asp script I can select * from newlymadequery..

and thats that.. but surely there is a way to do the above in asp.

Tks.