SQL with calculation based on multiple tables

Question

I have a simple DB with the following two tables.

There is a one-to-many relationship between the id field in the sessions table and the session_id field in the candidates table.

I'd like a query that will SELECT * FROM SESSIONS.

SESSIONS (TABLE)

|| id || title || max_candidates || description ||
|| 01 || fish  || 05             || some string ||
|| 02 || birds || 10             || some string ||

CANDIDATES (TABLE)

|| session_id || user_id ||
|| 01         || user01  ||
|| 02         || user12  ||
|| 02         || user03  ||
|| 02         || user05  ||

However, in addition to returning the information from the sessions table, I'd also like it to return a calculated column named avaliable_spaces.

I'd like to have this column return the number of avalaible spaces for a particular session based on (no of times session_id occurs in CANDIDATES) - (max_candidates)

In the above example it would return (obviously minus the column headders);

|| id || title || max_candidates || description || avaliable_spaces ||

|| 01 || fish  || 05             || some string ||04                ||
|| 02 || birds || 10             || some string ||07                ||

Does this make sense? And if so, is it even possible!? (as you can probably guess) I'm an SQL noob and this is well beyond me!

Hi, no - its an 'I have extremely limited experience of SQL problem'. Many thanks for your input, I'll give it a whirl on Monday. — user469453
– user469453, Commented Feb 11, 2011 at 18:31

Martin Smith · Accepted Answer · 2011-02-11 21:06:59Z

6

SELECT S.id,
       S.title,
       S.max_candidates,
       S.description,
       S.max_candidates - COUNT(c.user_id) as available_spaces
FROM   SESSIONS S
       LEFT OUTER JOIN CANDIDATES C
         ON C.session_id = s.id
GROUP  BY S.id,
          S.title,
          S.max_candidates,
          S.description

edited Feb 11, 2011 at 21:06

answered Feb 11, 2011 at 17:41

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

12 Comments

vol7ron Over a year ago

no need for the OUTER, left joins are inherently outer joins.

Martin Smith Over a year ago

@vol7ron No reason not to include it either. It's a matter of taste.

Martin Smith Over a year ago

@vol7ron - lol it doesn't make it any more difficult to interpret or convoluted, ugliness is clearly subjective and your argument that the optional OUTER keyword should not be used due to the effect on file size is frankly laughable. Do you give all your tables and columns one character names for similar reasons? You should be pleased at the lack of formatting anyway. White space takes up valuable bytes :-)

vol7ron Over a year ago

@Martin: the fact that you consider my comment to apply to only one join statement for one query in one project is "laughable". The fact that you don't consider nested joins, or complicated recursive statements shows your experience. Yes, extra keywords do make it harder to read, your argument is without base. White space doesn't take up any extra bytes, whereas if I chose to use OUTER every place there was a full, left, or right join, it would add up if you have many projects as I do. And, unfortunately, space conservation is still important when you buy it at contracted prices.

vol7ron Over a year ago

@Whomever is subtracting points: I'm not saying that his solution is worth a -1 as I do agree that the formatting and legibility is subjective, however there have been studies to show that given two, equal, common phrases; it's easier to interpret the one with fewer words.

|

gbn · Accepted Answer · 2011-02-11 17:47:53Z

1

SELECT
    S.*, S.max_candidates - C.Filled AS avaliable_spaces
FROM
    SESSIONS S
    LEFT JOIN
    (
    SELECT session_id, COUNT(*) AS Filled FROM CANDIDATES GROUP BY session_id
    ) C ON S.ID = C.session_id

edited Feb 11, 2011 at 17:47

answered Feb 11, 2011 at 17:42

gbn

435k85 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

SQL with calculation based on multiple tables

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related