Hi,
My question hinges off the same type of DB as the question asked on here a little while back (https://forums.oracle.com/thread/1982906).
The specific question is: Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books
I am trying to build a multiple-row subquery (I'm not sure if this would be the best way to approach). I supplied the table at the bottom of this post.
Per my code below, as you can see, this is not working for me. I'm trying to pull the total count of each book (via isbn inside table authorid), then I'll look-up the author code and ultimately the author's name (table orderitems then bookauthor) . I'm working from the inside out, and cannot get past using the first subquery.
Could someone please be able to help point me in the right direction?
Thanks for your help!
SQL> SELECT isbn, authorid
2 FROM bookauthor
3 WHERE isbn IN (SELECT COUNT(isbn)
4 FROM orderitems
5 GROUP BY isbn)
6 ORDER BY isbn, authorid;
no rows selected
TABLES:
SQL> select * from orderitems;
ORDER# ITEM# ISBN QUANTITY PAIDEACH
---------- ---------- ---------- ---------- ----------
1000 1 3437212490 1 19.95
1001 1 9247381001 1 31.95
1001 2 2491748320 1 85.45
1002 1 8843172113 2 55.95
1003 1 8843172113 1 55.95
1003 2 1059831198 1 30.95
1003 3 3437212490 1 19.95
1004 1 2491748320 2 85.45
1005 1 2147428890 1 39.95
1006 1 9959789321 1 54.5
1007 1 3957136468 3 72.15
1007 2 9959789321 1 54.5
1007 3 8117949391 1 8.95
1007 4 8843172113 1 55.95
1008 1 3437212490 2 19.95
1009 1 3437212490 1 19.95
1009 2 0401140733 1 22
1010 1 8843172113 1 55.95
1011 1 2491748320 1 85.45
1012 1 8117949391 1 8.95
1012 2 1915762492 2 25
1012 3 2491748320 1 85.45
1012 4 0401140733 1 22
1013 1 8843172113 1 55.95
1014 1 0401140733 2 22
1015 1 3437212490 1 19.95
1016 1 2491748320 1 85.45
1017 1 8117949391 2 8.95
1018 1 3437212490 1 19.95
1018 2 8843172113 1 55.95
1019 1 0401140733 1 22
1020 1 3437212490 1 19.95
SQL> select * from bookauthor;
ISBN AUTH
---------- ----
0132149871 S100
0299282519 S100
0401140733 J100
1059831198 P100
1059831198 S100
1915762492 W100
1915762492 W105
2147428890 W105
2491748320 B100
2491748320 F100
2491748320 R100
3437212490 B100
3957136468 A100
4981341710 K100
8117949391 R100
8843172113 A100
8843172113 A105
8843172113 P105
9247381001 W100
9959789321 J100
20 rows selected.
SQL> select * from author;
AUTH LNAME FNAME
---- ---------- ----------
S100 SMITH SAM
J100 JONES JANICE
A100 AUSTIN JAMES
M100 MARTINEZ SHEILA
K100 KZOCHSKY TAMARA
P100 PORTER LISA
A105 ADAMS JUAN
B100 BAKER JACK
P105 PETERSON TINA
W100 WHITE WILLIAM
W105 WHITE LISA
R100 ROBINSON ROBERT
F100 FIELDS OSCAR
W110 WILKINSON ANTHONY
14 rows selected.