Solved

how to select from another query

Posted on 2006-11-01
12
309 Views
Last Modified: 2012-05-05
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
Comment
Question by:Ricky11
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 4

Expert Comment

by:yeroket
ID: 17855902
SELECT DISTINCT REFRENCE ,
(SELECT     TOP 1 size FROM Products t2 WHERE      REFRENCE = Products .REFRENCE ) AS size
FROM Products
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17855918
or make the query simply like this:

select reference, min(size) size from products group by reference
0
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17856005
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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 4

Expert Comment

by:yeroket
ID: 17856229
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17856259
>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
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17856390
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
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17856403
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
 

Author Comment

by:Ricky11
ID: 17856475
Yes first value.

I will check out the code soon.

Tks ppls.
0
 
LVL 6

Accepted Solution

by:
Dragonlaird earned 500 total points
ID: 17857625
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
 

Author Comment

by:Ricky11
ID: 17865621
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
 

Author Comment

by:Ricky11
ID: 17865645
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question