Solved

how to select from another query

Posted on 2006-11-01
12
307 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now