Pulling in results from one query into the next

Hi guys

I have two sql queries on my page which queries two separate car tables. As an example, the first query simply queries table1 and returns any car models that are manufactured by Aston Martin.


<%
sql = "Select model from table1 where manufacturer = 'aston martin'"

oRs.Open sql, oDBConn 
If oRs.eof then

  Else

        Do while not oRs.eof

manufacturer = oRs("manufacturer")

            oRs.MoveNext
        loop 

  End If
%>

So, I would get results like:

DB9
DB7
Vantage
Vanquish
DB5

so far so good…

The second query searches table2 for any records which match a set of car models, it would then display the top speed for each of those models. So…


<%
sql = "Select model, topspeed from table2 WHERE model IN ('###', '###', '###')"

oRs.Open sql, oDBConn 
If oRs.eof then

  Else

        Do while not oRs.eof
%>
<%=oRs("model")%> = <%=oRs("topspeed")%>
<%

            oRs.MoveNext
        loop 

  End If
%>

Here’s where I have my problem. I need to pull in the models from the first query into the “IN” statement in the second query. Thus it is executed as follows:


sql = "Select model, topspeed from table2 WHERE model IN ('DB9', 'DB7', 'Vantage', 'Vanquish', 'DB5')"

There is no set number of models. So, For a Porsche query, I may only have 3 models but for Audi I may have 13 models that need to be pulled into the second query.

I am an utter complete novice and have been looking at how I can do this for the past 3 days! The closest I have come is something called an array but this is way above my head. Is there an “easy” solution to this? Any help would be really appreciated.

I look forward to hearing from you

Best regards

Rod from the UK

SELECT t1.model, t2.topspeed
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t2.model = t1.model AND t1.model = 'aston martin'

This should get you everything from both tables for all Aston Martin cars.

HTH,

:slight_smile:

Thanks WolfShade - This worked perfectly!!

One last thing if I may. I also have a “price” field in table2. If I wanted to rank my results by price from low to high, how would I do this? I’m guessing it’s not as simple as:

SELECT t1.model, t2.topspeed
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t2.model = t1.model AND t1.model = ‘aston martin’ AND ORDER BY PRICE DESC

Again, any help would be really appreciated

Best regards

Rod from the UK

Hi, @rjoseph244510,

You were SO close with that!! Take out the last AND, because the ORDER BY is separate from the JOIN.

Also, since PRICE is in table2 (which is aliased as t2), you should indicate as much. (If there are NO other tables in the JOIN that have a column named PRICE, it won’t be necessary; however, I’m anal-retentive about good form, so it’s just generally a good idea to use aliases. Think of it as “pre-emptive disambiguation”.)

SELECT t1.model, t2.topspeed
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t2.model = t1.model AND t1.model = 'aston martin' 
ORDER BY t2.price DESC

HTH,

:slight_smile:

this is brilliant, i’m a steal this

you have a column in the ORDER BY that isn’t in the SELECT, which i believe is actually non-standard sql, although i know several database systems will run it… but i prefer to have it in the SELECT in any case, because that will make it absolutely clear what the results are sorted by

and Rod from UK wanted to rank results by price from low to high, so that would be ASC, not DESC

also, you have a condition in your ON clause that i feel should be in the WHERE clause, like this –

SELECT t1.model , t2.topspeed , t2.price FROM table1 AS t1 LEFT OUTER JOIN table2 AS t2 ON t2.model = t1.model WHERE t1.model = 'aston martin' ORDER BY t2.price ASC

It depends upon the DB server, I believe. I should have asked the OP which one is being used. MS-SQL will run this with no problem; Oracle, on the other hand, will not give everything and will not error. I’ve learned this the difficult way.

And I agree with your assessment that price should be in the select, even if it won’t be displayed.

V/r,

:slight_smile:

The query as written in #4 is standard compliant.

As long as distinct is not used in the outmost select clause or a set operator (union, intersect or except) is not present in the outmost query, columns not present in the select list may be used in the order by clause.

Guys, thank you soooo much!

I’ve finally been able to crack on with my project!

Thanks again!

Best regards

Rod from the UK

1 Like

Hi

WolfShade helped me so much on this. However, it was only when I added paging to my results that I realised that the solution doesn’t quite do what I need it to. Based on WolfShade’s example, this is what I am currently using:


SELECT t1.model, t2.topspeed
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t2.model = t1.model AND t1.model = ‘aston martin’
ORDER BY t2.price DESC

However, it’s actually returning results from both tables when I actually only want it to return results from table2. Therefore, when I “page” or “count” the results of the query it is including results from table1.

How can I adapt the script so that it only returns results in table2?

Again, any help would be fully appreciated.

Best regards

Rod from the UK

Hi guys

I was able to use:

select model, topspeed from table2 where model in (select model from table1 where manufacturer = ‘aston martin’)

which did the job

Thanks

Rod from the UK

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.