SELECT from multiple queries

Question

I have this tables:

tblDiving(
diving_number int primary key
diving_club int
date_of_diving date)

tblDivingClub(
number int primary key not null check (number>0),
name char(30),
country char(30))

tblWorks_for(
diver_number int
club_number int
end_working_date date)

tblCountry(
name char(30) not null primary key)

I need to write a query to return a name of a country and the number of "Super club" in it. a Super club is a club which have more than 25 working divers (tblWorks_for.end_working_date is null) or had more than 100 diving's in it(tblDiving) in the last year. after I get the country and number of super club, I need to show only the country's that contains more than 2 super club.

I wrote this 2 queries:

select tblDivingClub.name,count(distinct tblWorks_for.diver_number) as number_of_guids
from tblWorks_for 
inner join tblDivingClub on tblDivingClub.number = tblWorks_for.club_number,tblDiving
where tblWorks_for.end_working_date is null
group by tblDivingClub.name

select tblDivingClub.name, count(distinct tblDiving.diving_number) as number_of_divings
from tblDivingClub
inner join tblDiving on tblDivingClub.number = tblDiving.diving_club
WHERE tblDiving.date_of_diving <= DATEADD(year,-1, GETDATE())
group by tblDivingClub.name

But I don't know how do I continue. Every query works separately, but how do I combine them and select from them? It's university assignment and I'm not allowed to use views or temporary tables.

It's my first program so I'm not really sure what I'm doing:)

try using a UNION ALL

JsonStatham
– JsonStatham

2014-08-08 08:53:41 +00:00
Commented Aug 8, 2014 at 8:53 — JsonStatham
– JsonStatham, Commented Aug 8, 2014 at 8:53

JsonStatham · Accepted Answer · 2014-08-08 09:00:06Z

2

WITH CTE AS (

select tblDivingClub.name,count(distinct tblWorks_for.diver_number) as diving_number
from tblWorks_for 
inner join tblDivingClub on tblDivingClub.number = tblWorks_for.club_number,tblDiving
where tblWorks_for.end_working_date is null
group by tblDivingClub.name

UNION ALL

select tblDivingClub.name, count(distinct tblDiving.diving_number) as diving_number
from tblDivingClub
inner join tblDiving on tblDivingClub.number = tblDiving.diving_club
WHERE tblDiving.date_of_diving <= DATEADD(year,-1, GETDATE())
group by tblDivingClub.name
)

SELECT * FROM CTE

You can combine the queries using a UNION ALL as long as there are the same number of columns in each query. You can then roll them into a Common Table Expression (CTE) and do a select from that.

edited Aug 8, 2014 at 9:00

answered Aug 8, 2014 at 8:54

JsonStatham

10.5k29 gold badges113 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hila Gal Over a year ago

I tried. I got a list of Country and a number. I can see every Country twice, first with a number from the first query and second with a number from the second query. But how do I continue from this point?

Hila Gal Over a year ago

When I'm trying to add: having number_of_guids>25 to the first query, I get 'Invalid column name number_of_guids' Why is that?

JsonStatham Over a year ago

you need to have the same number of columns in each query that makes up the CTE, you can add additional columns to the bottom most query though

Hila Gal Over a year ago

And after I found all "super clubs", How do I group them by country?

JsonStatham Over a year ago

You currently don't have a column named 'Country' in your two SELECTS within the CTE. You would need to introduce that field to both queries then do a GROUP BY in the CTE SELECT at the bottom.

Collectives™ on Stack Overflow

SELECT from multiple queries

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related