How to run multiple SELECT Statements?

Question

Post reply

How to run multiple SELECT Statements?

rob_brown1734

SSC Veteran

Points: 262
More actions
March 20, 2012 at 4:25 pm

#266066

I was hoping to get some assistance with the query below. I was able to create the individual SELECT statements against the Pubs database in SQL Server 2008 R2, but I am not sure how to join them as one select query so the results come up in one table and not three individual tables:
Scenerio:
Add a view called StoreSales_vw in the Pubs database that shows the store name from all stores in California that match this criteria in the sales table:
•sales with quantities greater than 20
•payterms greater than 'Net 30'.
Run a SELECT query on the view.
My Work:
SELECT * FROM stores WHERE state= 'CA'
SELECT * FROM sales WHERE payterms='Net 30'
SELECT * FROM sales WHERE qty >=’20’

Viewing 7 posts - 1 through 6 (of 6 total)

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

andersg98 SSCertifiable Points: 5180 More actions · Answer 1

I don't have a copy of PUBS and your desired result is unclear.

Might be something like:

SELECT *

FROM stores

LEFT JOIN Sales

ON stores.<whatever the KEY is FOR Sales TABLE> = Sales.<whatever the KEY is FOR Sales TABLE>

WHERE Stores.[state]= 'CA' AND Sales.payterms='Net 30' AND Sales.qty >=’20’

or

SELECT *

FROM stores WHERE state= 'CA'

Union

SELECT * FROM sales WHERE payterms='Net 30'

Union

SELECT * FROM sales WHERE qty >=’20’

But we need much more direction to help you get the desired results.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

Hi. That's a good answer to the question.

But maybe the first example will not show all possible results from the three queries because you used "WHERE option1 AND option2 AND option3"? Or am I missing the point here?

To the OP: If you use UNION or UNION ALL, you'll probably want to specify what are the columns you want to retrieve because the tables Stores and Sales can have a different number of columns or they may be in a different order. 😉

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

CapnHector SSCoach Points: 16143 More actions · Answer 3

from the OP's question it seems like he is very new to sql

here are 2 articles on joins, inner and outer.

Inner Joins - http://msdn.microsoft.com/en-us/library/ms190014.aspx

Outer Joins - http://msdn.microsoft.com/en-us/library/ms187518.aspx

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jared SSC Guru Points: 61793 More actions · Answer 4

To build on what others are saying:

UNION or UNION ALL will combine 2 or more result sets vertically; essentially "stacking" them on top of each other. The # of columns from result set 1 and result set 2 must be the same and the data types of the corresponding columns should be the same or compatible. UNION removes "true" duplicates while UNION ALL does not.

JOINs (INNER, OUTER, etc.) combines 2 or more result sets horizontally so as to include columns from one or more result sets with the first. Here, you must define a relationship between the 2 result sets.

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I don't have the Pubs database, can you please provide the DDL for the tables involved.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

rob_brown1734 (3/20/2012)
I was hoping to get some assistance with the query below. I was able to create the individual SELECT statements against the Pubs database in SQL Server 2008 R2, but I am not sure how to join them as one select query so the results come up in one table and not three individual tables:
Scenerio:
Add a view called StoreSales_vw in the Pubs database that shows the store name from all stores in California that match this criteria in the sales table:
•sales with quantities greater than 20
•payterms greater than 'Net 30'.
Run a SELECT query on the view.
My Work:
SELECT * FROM stores WHERE state= 'CA'
SELECT * FROM sales WHERE payterms='Net 30'
SELECT * FROM sales WHERE qty >=’20’

I am still waiting for you post the DDL (CREATE TABLE statements) for the tables from the PUBS database. I can't help you if I don't know the structure of the tables and I don't have a copy of the database you are using.