CODEWITHSUNDEEP
sqlsql-server-2008
Michael Seltenreich
Michael Seltenreich

Reputation: 3508

how to do get multiple columns + count in a single query?

I usually don't ask for "scripts" but for mechanisms but I think that in this case if i'll see an example I would understand the principal.

I have three tables as shown below:

enter image description here

and I want to get the columns from all three, plus a count of the number of episodes in each series and to get a result like this:

enter image description here

Currently, I am opening multiple DB threads and I am afraid that as I get more visitors on my site it will eventually respond really slowly.

Any ideas?

Thanks a lot!

Upvotes: 0

Views: 104

Answers (4)

Rachcha
Rachcha

Reputation: 8816

I would do the following:

SELECT (SELECT Count(*) 
        FROM   episodetbl e1 
        WHERE  e1.ofseries = s.seriesid) AS "#ofEpisodesInSeries", 
       s.seriesid, 
       s.seriesname, 
       e.episodeid, 
       e.episodename, 
       c.creatorid, 
       c.creatorname 
FROM   seriestbl s 
       INNER JOIN creatortbl c 
               ON s.bycreator = c.creatorid 
       INNER JOIN episodetbl e 
               ON e.ofseries = s.seriesid

Upvotes: 1

Sparky
Sparky

Reputation: 15125

Try this:

http://www.sqlfiddle.com/#!3/5f938/17

select min(ec.num) as NumEpisodes,s.Id,S.Name,
       Ep.ID as EpisodeID,Ep.name as EpisodeName,
       C.ID as CreatorID,C.Name as CreatorName
from Episodes ep
join Series s on s.Id=ep.SeriesID
join Creators c on c.Id=s.CreatorID
join (select seriesId,count(*) as Num from Episodes 
      group by seriesId) ec on s.id=ec.seriesID
group by  s.Id,S.Name,Ep.ID,Ep.name,C.ID,C.Name

Thanks Gordon

Upvotes: 2

Todd Bowles
Todd Bowles

Reputation: 1574

Do your appropriate joins between the tables and their IDs as you would expect, and also join onto the result of a subquery that determines the total episode count using the Episodes table.

SELECT SeriesCount.NumEpisodes AS #OfEpisodesInSeries, 
    S.id AS SeriesId, 
    S.name AS SeriesName, 
    E.id AS EpisodeId, 
    E.name AS EpisodeName,
    C.id AS CreatorId,
    C.name AS CreatorName
FROM
Series S
INNER JOIN
Episodes E
ON E.seriesId = S.id
INNER JOIN
Creators C
ON S.creatorId = C.id
INNER JOIN
(
    SELECT seriesId, COUNT(id) AS NumEpisodes
    FROM Episodes
    GROUP BY seriesId
) SeriesCount
ON SeriesCount.seriesId = S.id

SQL Fiddle Schema:

CREATE TABLE Series (id int, name varchar(20), creatorId int)

INSERT INTO Series VALUES(1, 'Friends', 1)
INSERT INTO Series VALUES(2, 'Family Guy', 2)
INSERT INTO Series VALUES(3, 'The Tonight Show', 1)

CREATE TABLE Episodes (id int, name varchar(20), seriesId int)

INSERT INTO Episodes VALUES(1, 'Joey', 1)
INSERT INTO Episodes VALUES(2, 'Ross', 1)
INSERT INTO Episodes VALUES(3, 'Phoebe', 1)
INSERT INTO Episodes VALUES(4, 'Stewie', 2)
INSERT INTO Episodes VALUES(5, 'Kevin Kostner', 3)
INSERT INTO Episodes VALUES(6, 'Brad Pitt', 3)
INSERT INTO Episodes VALUES(7, 'Tom Hanks', 3)
INSERT INTO Episodes VALUES(8, 'Morgan Freeman', 3)

CREATE TABLE Creators (id int, name varchar(20))

INSERT INTO Creators VALUES(1, 'Some Guy')
INSERT INTO Creators VALUES(2, 'Seth McFarlane')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

First join all the tables together to get the columns. Then, to get a count, use a window function:

SELECT count(*) over (partition by seriesID) as NumEpisodesInSeries,
       st.SeriesId, st.SeriesName, et.episodeID, et.episodeName,
       ct.createdID, ct.CreatorName
FROM series_table st join
     episode_table et
     ON et.ofSeries = st.seriesID join
     creator_table ct
     ON ct.creatorID = st.byCreator;

Upvotes: 2

Related Questions