?
Solved

Combine two SQL queries

Posted on 2007-11-29
5
Medium Priority
?
3,008 Views
Last Modified: 2008-02-01
I have two SQL statements that I need to combine into one but I don't know the proper syntax.

The 2 statements are as follows:

Query1:
SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User

Query2:
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
ORDER BY Ranking DESC

The inner select counts records in Query1 where the totalmiles is greater than the current record. i.e. if there are 4 other users that have travelled further, then my ranking is.. 4, add 1 = 5.
0
Comment
Question by:lepirtle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 750 total points
ID: 20374553

SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
union
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20374564

you need to create a view (querydef) with the first query, say with the name user_total_miles :

and you final query:

SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from user_total_miles where TotalMiles > Q1.TotalMiles) AS Ranking
FROM user_total_miles S Q1
 ORDER BY Ranking DESC



0
 

Author Comment

by:lepirtle
ID: 20375019
Thanks for your incredibly quick responses. I have been experimenting with your solutions trying to figure out how to utilize them in my "single" query but still cannot produce the results I need. I believe it is because I must "nest" the Q1 somewhere within the query itself and I don't know how do that.

For example, using sql_server_dba's solution:
SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
union
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
Don't I have to actually insert the Q1 query somewhere inside the above solution since the solution doesn't "know" what "Q1.user" or "Q1.TotalMiles" is?

And to use angelIII's solution requires using a "querydef". I don't know what a "querydef" is.

Sorry to be so unintelligent regarding this subject.

If it matters, I am using a MS Access database accessed using Coldfusion and Coldfusion is expecting to see something like:
<cfquery name=query" datasource="dsn_name"
SQL statement
</cfquery>
That is why I am trying to combine the 2 SQL statements into one - so that they can be inserted into the single Coldfusion query tag.

I hope this explains my reason for finding the syntax to combine all the SQL into one statement.
Thanks,
Lee
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 750 total points
ID: 20375643
a querydef in access is a what is otherwise called a VIEW.

SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from ( SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User ) where TotalMiles > Q1.TotalMiles) AS Ranking
FROM  ( SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User ) S Q1
 ORDER BY Ranking DESC
0
 

Author Comment

by:lepirtle
ID: 20375970
Thanks sql_server_dba and angelIII. I still don't know how to combine the queries into one SQL statement so that it will work in my Coldfusion query but I will continue to experiment with your solutions because I am sure that it is my lack of knowledge in understanding what you have given me that is the source of my problem.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

765 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