combine two queries into one

Question

Post reply

combine two queries into one

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

something like this i think?

select

Rented.make,

Rented.model,

Rented.year,

Rented.location,

Rented.agent,

Rented.status,

Returned.status

from Rentals Rented

LEft Outer Join Rentals Returned

ON Rented.make = Returned.make

AND Rented.model = Returned.model

AND Rented.year = Returned.year

AND Rented.location = Returned.location

AND Rented.agent = Returned.agent

where Rented.status = 'rented'

and (Returned.status = 'returned'

or Returned.status is null)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 2

SQL_NuB (6/14/2012)
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, 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?
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.

Then you are not giving us the entire query. What else is missing?

Jared
CE - Microsoft

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 3

SQLKnowItAll (6/14/2012)
SQL_NuB (6/14/2012)
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, 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?
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.
Then you are not giving us the entire query. What else is missing?

that's it, that's the query as I copied it from the MS Access applicatioin.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

SQL_NuB (6/14/2012)
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, 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?
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.

Then we aren't getting the whole story. Combining the WHERE clause to have STATUS = 'RETURNED' or STATUS = 'RENTED" should return the same number of records as each of the individual queries run separately.

Run the following:

CREATE TABLE #TestTable(

TestID INT IDENTITY(1,1),

[Status] CHAR(16));

INSERT INTO #TestTable([Status])

SELECT 'Returned' UNION ALL

SELECT 'Rented' UNION ALL

SELECT 'Stored' UNION ALL

SELECT 'Returned' UNION ALL

SELECT 'Stored' UNION ALL

SELECT 'Rented' UNION ALL

SELECT 'Returned';

SELECT * FROM #TestTable;

SELECT * FROM #TestTable WHERE [Status] = 'Returned';

SELECT * FROM #TestTable WHERE [Status] = 'Rented';

SELECT * FROM #TestTable WHERE [Status] = 'Returned' OR [Status] = 'Rented';

DROP TABLE #TestTable;

auaiomrn Ten Centuries Points: 1300 More actions · Answer 5

I'd be interested to see an example of a row that is returned using the single statement that is not returned when the statements are separated.

As has been mentioned, that shouldn't be possible.

Jared SSC Guru Points: 61793 More actions · Answer 6

And please don't misunderstand... We are not saying that you are lying or incompetent or anything. We are saying that something is unbeknownst to us AND probably you that is causing these results. Just wanted to make that clear.

Jared
CE - Microsoft

Jayaram Krishnaswamy SSC Eights! Points: 970 More actions · Answer 7

Jayaram Krishnaswamy

SSC Eights!

Points: 970

June 14, 2012 at 2:26 pm

#1501279

Very Strange! Both OR and Union All should work.;-)

EL Jerry SSCertifiable Points: 7058 More actions · Answer 8

Really strange!!

As Lynn and Jared have stated, the different WHERE clauses use the same logic, therefore they should return the same results.

Just out of curiosity, can you let us know if any of the following queries return the 346 rows you are expecting?

-- Query 1

select make, model, year, location, agent

from Rentals

where (status = 'returned') OR (status = 'rented')

-- Query 2

select make, model, year, location, agent

from Rentals

where status in ('returned', 'rented')

-- Query 3

-- The following may bring other statuses like 'reinstated', 'restored', etc., if these exist

select status, make, model, year, location, agent

from Rentals

where status like 're%t%ed'

-- Query 4

select make, model, year, location, agent

from Rentals

where status = 'returned'

union all

select make, model, year, location, agent

from Rentals

where status = 'rented'

Also, you say these queries return 16 and 330 records when run: in Access or SQL?

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 9

I'll be looking at it again today.

I get the 16 and 330 when i run the queries in the access app and the against the new sql table I created for these records

Jayaram Krishnaswamy SSC Eights! Points: 970 More actions · Answer 10

This is another thing you can do.

You say your MS Access query is working. In the design view of the query copy the SQL and compare with the SQL Query you are trying in SQL Server. Do you find any difference?