We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

how to select from another query

Ricky11
Ricky11 asked
on
Medium Priority
329 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.
Comment
Watch Question

Commented:
SELECT DISTINCT REFRENCE ,
(SELECT     TOP 1 size FROM Products t2 WHERE      REFRENCE = Products .REFRENCE ) AS size
FROM Products
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
or make the query simply like this:

select reference, min(size) size from products group by reference
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.

Commented:
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]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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
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...
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]

Author

Commented:
Yes first value.

I will check out the code soon.

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

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

???

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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....

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.