Sub Querying 2 Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sub Querying 2 Tables

Author

Topic

gretty
Starting Member

6 Posts

Posted - 2009-10-23 : 21:55:13

I have this question that is really stumping me. I need to find the names of all authors who have written more than one book.
Can you give me any advice on where I'm going wrong?

quote:
List the full names (first name followed by last name from table author) of authors who have written more than one title. The list should be sorted by last name and then first name (as a secondary sort key).

The tables(with their colums) involved are:

quote:
Author:
-AUTHOR_NUM DECIMAL PRIMARY KEY,
-AUTHOR_LAST
-AUTHOR_FIRST

quote:
Wrote:
-BOOK_CODE
-AUTHOR_NUM DECIMAL
-SEQUENCE DECIMAL

I cannot get this to work..



select author_first, author_last
from author, wrote
where (select count(author_num) from wrote group by author_num) > 1 and author.author_num = wrote.author_num
order by author_last;

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 22:10:46

select a.author_first + ' ' + author_last as FullName
from author a
inner join wrote w on w.author_num = a.author_num
group by a.author_last, a.author_first
having count(*) > 1
order by a.author_last, a.author_first

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources