• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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.
0
Ricky11
Asked:
Ricky11
  • 4
  • 3
  • 2
  • +1
1 Solution
 
yeroketCommented:
SELECT DISTINCT REFRENCE ,
(SELECT     TOP 1 size FROM Products t2 WHERE      REFRENCE = Products .REFRENCE ) AS size
FROM Products
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or make the query simply like this:

select reference, min(size) size from products group by reference
0
 
DragonlairdCommented:
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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
yeroketCommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
 
DragonlairdCommented:
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...
0
 
DragonlairdCommented:
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]
0
 
Ricky11Author Commented:
Yes first value.

I will check out the code soon.

Tks ppls.
0
 
DragonlairdCommented:
Just a note... Isn't this the same question you asked here:

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_22045511.html

???
0
 
Ricky11Author Commented:
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....
0
 
Ricky11Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now