help with joining two 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

SQL Server 2008 Forums

Transact-SQL (2008)

help with joining two tables

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2013-03-05 : 13:21:22

Hi
There is some tables :

Author ( AuthorID , AuthorFirstName , AuthorLastName )
BookTitle ( ISBN_Number , Title , PublisherID , Date_of_Published , Pages , Price , Comment )
Publisher ( PublisherID , PublisherName )
Category ( CategoryID , CategoryDescription )
Book_Author ( ISBN_Number , AuthorID )
Book_Category ( ISBN_Number,CategoryID )

I want to select the name of the Author who has the minimum compliation.
Please help me with the query

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 13:34:26

[code]SELECT TOP (1) WITH TIES
a.AuthorID,
a.AuthorFirstName,
a.AuthorLastName,
COUNT(*) AS Compilations
FROM
Author a
INNER JOIN Book_Author b ON
a.AuthorID = b.AuthorID
GROUP BY
a.AuthorID,
a.AuthorFirstName,
a.AuthorLastName
ORDER BY
Compilations ASC;
[/code]Can there be authors with no publications at all? If there are, are those the ones you want to pick?

Exir
Posting Yak Master

151 Posts

Posted - 2013-03-05 : 13:47:02

Thank you so much for your answer

quote:
Originally posted by James K
Can there be authors with no publications at all? If there are, are those the ones you want to pick?

yes, if there is some authors with no publications, those authors should be chosen

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 14:12:53

Ok - make the changes shown in red

SELECT TOP (1) WITH TIES
	a.AuthorID,
	a.AuthorFirstName,
	a.AuthorLastName,
	COUNT(b.ISBN_Number) AS Compilations
FROM
	Author a
	LEFT JOIN Book_Author b ON
		a.AuthorID = b.AuthorID
GROUP BY
	a.AuthorID,
	a.AuthorFirstName,
	a.AuthorLastName
ORDER BY
	Compilations ASC;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources