Simple query from Pubs database confusing me

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 2000 Forums

Transact-SQL (2000)

Simple query from Pubs database confusing me

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-15 : 17:46:53

Just procaticing some join exercises, and this one has me stumped. I want to return the following:


SELECT 
	au_lname AS 'First name', 
	au_fname AS 'Last Name',
	COUNT(ta.au_id) AS '# books',
	MIN(t.pubdate) AS 'First Pub Date',
	'First Pub Title' AS 'First Pub Title'

FROM authors a LEFT OUTER JOIN titleauthor ta
	ON a.au_id = ta.au_id LEFT OUTER JOIN titles t
		ON ta.title_id = t.title_id

GROUP BY au_lname, au_fname

but obviously populate the 'First Pub Title' with the title of the first published book for that author.
And yes, I want the NULLs in there if no books exist for an author.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-15 : 18:03:38

This should give you an idea of how to do it:



SELECT a.au_fname, a.au_lname, d.Books, d.MinPubDate, t.title
FROM titles t
INNER JOIN titleauthor ta
ON t.title_id = ta.title_id
INNER JOIN authors a
ON ta.au_id = a.au_id
INNER JOIN
(
	SELECT
		a.au_id,
		COUNT(ta.au_id) AS Books,
		MIN(t.pubdate) As MinPubDate
	FROM authors a 
	LEFT OUTER JOIN titleauthor ta
	ON a.au_id = ta.au_id 
	LEFT OUTER JOIN titles t
	ON ta.title_id = t.title_id
	GROUP BY a.au_id
) d -- derived table
ON a.au_id = d.au_id AND t.pubdate = d.MinPubDate
ORDER BY a.au_lname

Tara

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-16 : 14:42:59

Thanks Tara. As usual, you point me in theright direction :)

SELECT a.au_fname, a.au_lname, d.book_count, d.first_pub_date
FROM authors a
INNER JOIN (
SELECT a.au_id,
COUNT(ta.au_id) AS book_count,
MIN(t.pubdate) As first_pub_date
FROM authors a
LEFT OUTER JOIN titleauthor ta ON a.au_id = ta.au_id
LEFT OUTER JOIN titles t ON ta.title_id = t.title_id
GROUP BY a.au_id
) d
ON a.au_id = d.au_id
LEFT OUTER JOIN titleauthor ta ON a.au_id = ta.au_id
LEFT OUTER JOIN titles t ON t.title_id = ta.title_id
WHERE pubdate = d.first_pub_date OR pubdate IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources