June 14, 2012 at 12:46 pm
I'm working on converting an access db to SQL and I came across several Access queries that are the same with the exception of 1 parameter in the where clause. How can I combine both queries into one and get the correct amount of records returned?
Right now query 1 returns 16 records and query 2 returns 330.
The queries look like this
query 1
select make, model, year, location, agent from Rentals where status = 'returned'
query 2
select make, model, year, location, agent from Rentals where status = 'rented'
is there a way to combine both of these and still get the same number of records back? I'm working on creating some reports
and I'm trying to find an easy way to convert them from MS Access to SQL Server and right now there are 28 seperate queries in the Accss version, I'd like to
shrink that down to 1 or a lot less then 28 and get the results I need
thanks
June 14, 2012 at 12:49 pm
Well, without DDL my only suggestion is to place them in a blender and hit puree. 🙂 Please provide us with DDL and sample data with sample expected output.
Jared
CE - Microsoft
June 14, 2012 at 12:51 pm
SQL_NuB (6/14/2012)
I'm working on converting an access db to SQL and I came across several Access queries that are the same with the exception of 1 parameter in the where clause. How can I combine both queries into one and get the correct amount of records returned?Right now query 1 returns 16 records and query 2 returns 330.
The queries look like this
query 1
select make, model, year, location, agent from Rentals where status = 'returned'
query 2
select make, model, year, location, agent from Rentals where status = 'rented'
is there a way to combine both of these and still get the same number of records back? I'm working on creating some reports
and I'm trying to find an easy way to convert them from MS Access to SQL Server and right now there are 28 seperate queries in the Accss version, I'd like to
shrink that down to 1 or a lot less then 28 and get the results I need
thanks
Why would both of those queries return the same amount of rows? Is that a constraint on the data? (I'm guessing no because that would make no sense...)
Jared
CE - Microsoft
June 14, 2012 at 1:02 pm
SQLKnowItAll (6/14/2012)
SQL_NuB (6/14/2012)
I'm working on converting an access db to SQL and I came across several Access queries that are the same with the exception of 1 parameter in the where clause. How can I combine both queries into one and get the correct amount of records returned?Right now query 1 returns 16 records and query 2 returns 330.
The queries look like this
query 1
select make, model, year, location, agent from Rentals where status = 'returned'
query 2
select make, model, year, location, agent from Rentals where status = 'rented'
is there a way to combine both of these and still get the same number of records back? I'm working on creating some reports
and I'm trying to find an easy way to convert them from MS Access to SQL Server and right now there are 28 seperate queries in the Accss version, I'd like to
shrink that down to 1 or a lot less then 28 and get the results I need
thanks
Why would both of those queries return the same amount of rows? Is that a constraint on the data? (I'm guessing no because that would make no sense...)
They don't return the same total records, one returns 16 and one returns 330 for a total of 346, I want to combine these queries into 1 query and still get 346 rows back.
June 14, 2012 at 1:08 pm
Use the OR operator?
select make, model, year, location, agent
from Rentals
where status = 'returned' OR status = 'rented'
My guess is that by the time you combine them all you will simply not need a where condition. What's the point? Are you going to filter after the fact?
Jared
CE - Microsoft
June 14, 2012 at 1:13 pm
[OR] clause didn't work, that's the first thing I tried prior to asking.
The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
June 14, 2012 at 1:21 pm
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
I'm confused. This didn't work (what ever that means):
select make, model, year, location, agent
from Rentals
where status = 'returned' OR status = 'rented'
But each of these does:
select make, model, year, location, agent
from Rentals
where status = 'returned'
select make, model, year, location, agent
from Rentals
where status = 'rented'
June 14, 2012 at 1:26 pm
Lynn Pettis (6/14/2012)
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
I'm confused. This didn't work (what ever that means):
select make, model, year, location, agent
from Rentals
where status = 'returned' OR status = 'rented'
But each of these does:
select make, model, year, location, agent
from Rentals
where status = 'returned'
select make, model, year, location, agent
from Rentals
where status = 'rented'
The [or] returned me too many records when I should only see 345 in total
June 14, 2012 at 1:31 pm
My guess is that it returned the data but the report doesn't know how to handle it in a single result set.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 1:31 pm
SQL_NuB (6/14/2012)
Lynn Pettis (6/14/2012)
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
I'm confused. This didn't work (what ever that means):
select make, model, year, location, agent
from Rentals
where status = 'returned' OR status = 'rented'
But each of these does:
select make, model, year, location, agent
from Rentals
where status = 'returned'
select make, model, year, location, agent
from Rentals
where status = 'rented'
The [or] returned me too many records when I should only see 345 in total
Hmm... That's quite impossible. What do you get with this?
select make, model, year, location, agent
from Rentals
where status = 'returned'
UNION ALL
select make, model, year, location, agent
from Rentals
where status = 'rented'
Again, it is impossible for those other to return separate data. Unless there is something else you are not telling us. Can you please provide DDL for the tables ans sample (not real) data?
Jared
CE - Microsoft
June 14, 2012 at 1:35 pm
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
How does it know which section to put it under if it is not filtered? Something is not right here...
Jared
CE - Microsoft
June 14, 2012 at 1:35 pm
SQLKnowItAll (6/14/2012)
SQL_NuB (6/14/2012)
Lynn Pettis (6/14/2012)
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
I'm confused. This didn't work (what ever that means):
select make, model, year, location, agent
from Rentals
where status = 'returned' OR status = 'rented'
But each of these does:
select make, model, year, location, agent
from Rentals
where status = 'returned'
select make, model, year, location, agent
from Rentals
where status = 'rented'
The [or] returned me too many records when I should only see 345 in total
Hmm... That's quite impossible. What do you get with this?
select make, model, year, location, agentfrom Rentals
where status = 'returned'
UNION ALL
select make, model, year, location, agent
from Rentals
where status = 'rented'
Again, it is impossible for those other to return separate data. Unless there is something else you are not telling us. Can you please provide DDL for the tables ans sample (not real) data?
How is it impossible? the where clause is different in each query so that would return different results. So I'm confused by that statement.
June 14, 2012 at 1:38 pm
SQL_NuB (6/14/2012)
SQLKnowItAll (6/14/2012)
SQL_NuB (6/14/2012)
Lynn Pettis (6/14/2012)
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
I'm confused. This didn't work (what ever that means):
select make, model, year, location, agent
from Rentals
where status = 'returned' OR status = 'rented'
But each of these does:
select make, model, year, location, agent
from Rentals
where status = 'returned'
select make, model, year, location, agent
from Rentals
where status = 'rented'
The [or] returned me too many records when I should only see 345 in total
Hmm... That's quite impossible. What do you get with this?
select make, model, year, location, agentfrom Rentals
where status = 'returned'
UNION ALL
select make, model, year, location, agent
from Rentals
where status = 'rented'
Again, it is impossible for those other to return separate data. Unless there is something else you are not telling us. Can you please provide DDL for the tables ans sample (not real) data?
How is it impossible? the where clause is different in each query so that would return different results. So I'm confused by that statement.
It is impossible because a query with where status = 'returned' combined with where status = 'rented' is the exact same as where status = 'returned' OR status = 'rented. It is also EXACTLY the same as WHERE status IN ('returned', 'rented'). The logic to return rows is exactly the same when your 2 queries are combined.
Jared
CE - Microsoft
June 14, 2012 at 1:39 pm
SQLKnowItAll (6/14/2012)
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
How does it know which section to put it under if it is not filtered? Something is not right here...
the where clause is the the filter, the report will be designed to show what data in that particular section.
There is no filter for the users to select from on the UI, everything is done behind the scenes.
These queries already work in MS Access, all i'm doing is bringing the tables, and queries to SQL due Access can no longer be used. All I'm doing is trying to combine some of them into 1 query and work removing some other ones.
June 14, 2012 at 1:41 pm
SQLKnowItAll (6/14/2012)
SQL_NuB (6/14/2012)
SQLKnowItAll (6/14/2012)
SQL_NuB (6/14/2012)
Lynn Pettis (6/14/2012)
SQL_NuB (6/14/2012)
[OR] clause didn't work, that's the first thing I tried prior to asking.The data returned is going to be used on reports so no filtering right now, just put on the report under that particular section.
I'm confused. This didn't work (what ever that means):
select make, model, year, location, agent
from Rentals
where status = 'returned' OR status = 'rented'
But each of these does:
select make, model, year, location, agent
from Rentals
where status = 'returned'
select make, model, year, location, agent
from Rentals
where status = 'rented'
The [or] returned me too many records when I should only see 345 in total
Hmm... That's quite impossible. What do you get with this?
select make, model, year, location, agentfrom Rentals
where status = 'returned'
UNION ALL
select make, model, year, location, agent
from Rentals
where status = 'rented'
Again, it is impossible for those other to return separate data. Unless there is something else you are not telling us. Can you please provide DDL for the tables ans sample (not real) data?
How is it impossible? the where clause is different in each query so that would return different results. So I'm confused by that statement.
It is impossible because a query with where status = 'returned' combined with where status = 'rented' is the exact same as where status = 'returned' OR status = 'rented. It is also EXACTLY the same as WHERE status IN ('returned', 'rented'). The logic to return rows is exactly the same when your 2 queries are combined.
I don't know then, because when I run the query with [OR] it returns me more rows then when I run the 2 queries seperate.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply