C2H50H C2H50H - 23 days ago 5
SQL Question

Creating unique Table to display different values from 3 other tables

I need to list authors first name, last name, and total quantity of their books that have sold. So virtually I need to connect au_id to title_id then connect the SUM(qty) of the sales that correspond with the books they have written.

Do I need to Inner Join? I feel like this is where I'm getting my errors.
Thanks

Tables:
AUTHORS TABLE

+-------+----------+----------+
| au_id | au_fname | au_lname |
+-------+----------+----------+


TITLEAUTHORS TABLE

+-------+---------
| au_id |title_id|
+-------+---------


SALES TABLE

+-------+---------
| title_id | QTY |
+-------+---------


Query:

SELECT au_fname, au_lname, TOT FROM(
SELECT au_fname, au_lname FROM authors
INNER JOIN titleauthors ON authors.au_id = titleauthors.au_id
INNER JOIN sales on titleauthors.title_id = sales.title_id WHERE titleauthors.title_id IN(
SELECT sales.title_id, SUM(sales.qty) TOT from sales
group by sales.title_id)) as t


Error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'TOT'.

Answer

As far as I know,

join statement can't be after another join statement.

And you can't pull out TOT like that.


Here is the query for you.

SELECT  A.*, TS.title_id, TS.TOT
FROM    AUTHORS A
INNER JOIN (
    SELECT  T.*, S.TOT
    FROM    TITLEAUTHORS T
    LEFT OUTER JOIN (
        SELECT   title_id, SUM( QTY ) TOT
        FROM     SALES
        GROUP BY title_id
    ) S
    ON T.title_id = S.title_id
) TS
ON A.au_id = TS.au_id

This will work.

Because it has outer join,

TOT can be null. You can solve that by using IF or something according to your database.


EDIT

If what you wanna do is total sum of sales for each authors regardless of what the title_id is,

then that query should be changed.

SELECT  A.au_fname, A.au_lname, SUM( TOT ) TOT
FROM    (
    SELECT  A.*, TS.title_id, TS.TOT
    FROM    AUTHORS A
    INNER JOIN (
        SELECT  T.*, S.TOT
        FROM    TITLEAUTHORS T
        LEFT OUTER JOIN (
            SELECT   title_id, SUM( QTY ) TOT
            FROM     SALES
            GROUP BY title_id
        ) S
        ON T.title_id = S.title_id
    ) TS
    ON A.au_id = TS.au_id
) A
GROUP BY au_id

Try this.

And let me know if it goes wrong with this query.

Comments