Sub query - 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 2005 Forums

Transact-SQL (2005)

Sub query

Author

Topic

bunny28
Starting Member

13 Posts

Posted - 2009-08-20 : 21:51:07

BOOK Table
BOOK_CODE TITLE PUBLISHER_CODE TYPE PRICE

180 A Deepness in the Sky TB SFI 7.19
189 Magic Terror FA HOR 7.99
200 The Stranger VB FIC 8
378 Venice SS ART 24.5
079X Second Wind PU MYS 24.95
808 The Edge JP MYS 6.99
1351 Dreamcatcher SC HOR 19.6
1382 Treasure Chests TA ART 24.46
138X Beloved PL FIC 12.95
2226 Harry Potter and the Prisoner of Azkaban ST SFI 13.95

WROTE Table
BOOK_CODE AUTHOR_NUM SEQUENCE

180 3 1
189 5 1
200 7 1
378 8 1
079X 4 1
808 4 1
1351 6 1
1382 2 2
1382 9 1
138X 1 1
2226 10 1

AUTHOR Table
AUTHOR_NUM AUTHOR_LAST AUTHOR_FIRST

1 Morrison Toni
2 O’Rouke Randy
3 Vintage Vernor
4 Francis Dick
5 Straub Peter
6 King Stephen
7 Camus Albert
8 Wills Gary
9 Schleining Lon
10 Rowling J.K.

PUBLISHER Table
PUBLISHER_CODE PUBLISHER_NAME CITY

TB Tor Books New York
FA Fawcett Books New York
VB Vintage Books New York
SS Simon & Schuster New York
PU Putnam Publishing House New York
JP Jove Publishing New York
SC Scribner New York
TA Tauton Press Newtown CT
PL Plume New York
ST Scholastic Trade New York

hi i have the above 4 tables

and i need query to

1) list the title of the books that have more than 1 author
2) list the title,authornames and publisher

please help me on this queries its urgent

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-20 : 22:02:21

1)
use GROUP BY . . . HAVING COUNT(*) > 1

2)
INNER JOIN the tables

KH
[spoiler]Time is always against us[/spoiler]

bunny28
Starting Member

13 Posts

Posted - 2009-08-20 : 22:10:46

Hi thanks for the reply

1) SELECT top 1 author_num
FROM wrote
GROUP BY author_num
HAVING count(*)>1

i am able to get upto the above query but i am not able to get the final one can u please me

2) i dont have idea on joins

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-20 : 22:22:59

STEP 1 : put the required column name in the SELECT

SELECT TITLE, AUTHOR_LAST, AUTHOR_FIRST, PUBLISHER_NAME

STEP 2 : find the tables where these columns comes from
It will be : BOOK, AUTHOR, PUBLISHER (TITLE is from BOOK, AUTHOR_LAST, AUTHOR_FIRST from AUTHOR etc)
this will form the FROM clause

STEP 3 : find the relationship between the tables (BOOK, AUTHOR, PUBLISHER)
BOOK link to PUBLISHER via PUBLISHER_CODE
you will notice that you are missing the link between BOOK and AUTHOR, so you need to get it from WROTE
WROTE link BOOK and AUTHOR together via BOOK_CODE & AUTHOR_NUM. The "link" is basically the JOIN condition

now put all these together


SELECT BOOK.title, AUTHOR.AUTHOR_LAST, AUTHOR.AUTHOR_FIRST, PUBLISHER.PUBLISHER_NAME
FROM   BOOK
       INNER JOIN PUBLISHER ON BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
       INNER JOIN WROTE     ON BOOK.BOOK_CODE      = WROTE.BOOK_CODE
       INNER JOIN AUTHOR    ON WROTE.AUTHOR_NUM    = AUTHOR.AUTHOR_NUM

KH
[spoiler]Time is always against us[/spoiler]

bunny28
Starting Member

13 Posts

Posted - 2009-08-20 : 22:29:43

hi can u please help me for the first query also.

i have the inner query, what ever way i try i am getting the cartesian values

Thank you

bunny28
Starting Member

13 Posts

Posted - 2009-08-20 : 22:52:27

hi for the first one i tried this query, problem i am getting is i can only choose top 1 from the inner query
and only one title i can print i shoukd get two according to data what i have.
please can u help me

select title from book where book_code=(
select top 1 book_code from wrote where author_num=(
SELECT top 1 author_num
FROM wrote
GROUP BY author_num
HAVING count(*)>1))

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources