what is wrong with this code? - 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

what is wrong with this code?

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-30 : 14:24:25

select au_lname, au_fname,au_id
from authors
where 100 =all
(select royaltyper
from titleauthor
where titleauthor.au_id = authors.au_id)
This query uses database pubs. Why this code returns some au_id which is not in table titleauthor?
for example 527-72-3246 is not in titleauthor.

how to find the names of all authors who earn 100 percent royalty on all his/her books?

Thanks

=================================================

au_lname au_fname au_id
Blotchet-Halls Reginald 648-92-1872
Carson Cheryl 238-95-7766
del Castillo Innes 712-45-1867
Greene Morningstar 527-72-3246
Locksley Charlene 486-29-1786
McBadden Heather 893-72-1158
Panteley Sylvia 807-91-6654
Smith Meander 341-22-1782
Straight Dean 274-80-9391
Stringer Dirk 724-08-9931
White Johnson 172-32-1176

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-30 : 14:29:59

what is 100=all suppose to be?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-30 : 14:56:04

THX,
The purpose of this query is to find the names of all authors who earn 100 percent royalty on all his/her books
Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 15:20:52

Yes, and where is the ROYALTY information?
Even if two people cowrite a book, nothing says they share it equal, 50-50 percent!

Rephrase your question.
Do you want all authors who are single authors of a book?

Peter Larsson
Helsingborg, Sweden

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-30 : 15:37:23

tabale authors
au_id au_lname au_fname
172-32-1176 White Johnson
213-46-8915 Green Marjorie
238-95-7766 Carson Cheryl
267-41-2394 O'Leary Michael
274-80-9391 Straight Dean
341-22-1782 Smith Meander
409-56-7008 Bennet Abraham
427-17-2319 Dull Ann
472-27-2349 Gringlesby Burt
486-29-1786 Locksley Charlene
527-72-3246 Greene Morningstar
648-92-1872 Blotchet-Halls Reginald
672-71-3249 Yokomoto Akiko
712-45-1867 del Castillo Innes
722-51-5454 DeFrance Michel
724-08-9931 Stringer Dirk
724-80-9391 MacFeather Stearns
756-30-7391 Karsen Livia
807-91-6654 Panteley Sylvia
846-92-7186 Hunter Sheryl
893-72-1158 McBadden Heather
899-46-2035 Ringer Anne
998-72-3567 Ringer Albert

Table titleauthor
au_id title_id au_ord royaltyper
172-32-1176 PS3333 1 100
213-46-8915 BU1032 2 40
213-46-8915 BU2075 1 100
238-95-7766 PC1035 1 100
267-41-2394 BU1111 2 40
267-41-2394 TC7777 2 30
274-80-9391 BU7832 1 100
409-56-7008 BU1032 1 60
427-17-2319 PC8888 1 50
472-27-2349 TC7777 3 30
486-29-1786 PC9999 1 100
486-29-1786 PS7777 1 100
648-92-1872 TC4203 1 100
672-71-3249 TC7777 1 40
712-45-1867 MC2222 1 100
722-51-5454 MC3021 1 75
724-80-9391 BU1111 1 60
724-80-9391 PS1372 2 25
756-30-7391 PS1372 1 75
807-91-6654 TC3218 1 100
846-92-7186 PC8888 2 50
899-46-2035 MC3021 2 25
899-46-2035 PS2091 2 50
998-72-3567 PS2091 1 50
998-72-3567 PS2106 1 100

I want find the name of find the names of all authors who have royaltyper
=100 for all his/her books

the result should look like
au_lname au_fname au_id
White Johnson 172-32-1176
Green Marjorie 213-46-8915
Carson Cheryl 238-95-7766
Straight Dean 274-80-9391
Blotchet-Halls Reginald 648-92-1872
del Castillo Innes 712-45-1867
Panteley Sylvia 807-91-6654

I want 486-29-1786 becauase both of his book has royaltyper=100
I do not want
Ringer Albert 998-72-3567
becase one of his books has royaltyper=50

Thnaks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 15:57:18

[code]SELECT a.au_lname,
a.au_fname,
a.au_id
FROM Authors AS a
INNER JOIN TitleAuthor AS t ON t.au_id = a.au_id
GROUP BY a.au_lname,
a.au_fname,
a.au_id
HAVING MIN(t.royaltyper) = 100
ORDER BY a.au_id[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 16:25:23

This can be an alternative, especially if the TitleAuthor table has records like

267-41-2394	PS2092	2	50
267-41-2394	PS2092	1	50

By mistake an author is registered twice or more for a single book, but is still single author.

SELECT		a.au_lname,
		a.au_fname,
		a.au_id
FROM		(
			SELECT		au_id,
					title_id,
					SUM(royaltyper) AS royalty
			FROM		@TitleAuthor
			GROUP BY	au_id,
					title_id
		) AS x
INNER JOIN	@Authors AS a ON a.au_id = x.au_id
GROUP BY	a.au_lname,
		a.au_fname,
		a.au_id
HAVING		MIN(royalty) = 100
ORDER BY	a.au_id

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 16:43:06

Now I know where I have seen you before!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80918
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80826
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80817
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80133

Peter Larsson
Helsingborg, Sweden

jeff06
Posting Yak Master

166 Posts

Posted - 2007-04-02 : 14:50:18

Peter,
Thank you for all help.
I am a guy in finance field, SQL is hard for me. I usally need to spend quite long to figure out how your code work. And I can figure out some.
I benefit a lot from this forum.
Jeff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources