|
|
| Re: Extract the most data after joining 3 tables [message #652512 is a reply to message #652509] |
Sat, 11 June 2016 04:27   |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Your question doesn't make a lot of sense to me. I have no idea why you want to select only Tanye Wayne. However, I am reasonably certain that you need to read the documentation describing the GROUP BY ... HAVING ... clause. Your answer is probably in there somewhere.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Extract the most data after joining 3 tables [message #652520 is a reply to message #652509] |
Sat, 11 June 2016 07:30 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
Hmm, based on your previous post, I have a feeling that this might be homework of some sort. Can you post the actual question that has been asked of you (along with the test case that Michel asked for). Unfortunately, the way that you are currently asking the question is very unclear.
|
|
|
|
| Re: Extract the most data after joining 3 tables [message #652526 is a reply to message #652509] |
Sat, 11 June 2016 16:47 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I gather that you want the name of the buyer who has purchased books by the most authors. It helps if you supply sample data, in addition to the sample results. It appears that TableC is not needed for this query, just TableA and TableB. Without sample data, we can't tell if you might have multiple buyers with different dates in TableA and/or multiple entries for the same combinations of buyers and authors in TableB, so I will assume that you might have both. As this appears to be homework, I have deliberately left some of the code out, indicated by ... for you to figure out. There are various ways to do this. I have used the one that seems closest to what you were trying to do and is what others seemed to be suggesting and is a common beginner's method that you are probably expected to use.
Suppose you have data like this:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TableA
2 /
BUYERID LASTNAME FIRSTNAME PURCHASE_DATE
---------- --------------- --------------- ---------------
1 Bill Mike Sat 11-Jun-2016
1 Bill Mike Sat 11-Jun-2016
2 Laura Ryan Sat 11-Jun-2016
3 Tanya Wayne Sat 11-Jun-2016
3 Tanya Wayne Sat 11-Jun-2016
4 Jane James Sat 11-Jun-2016
5 Pete Lone Sat 11-Jun-2016
7 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TableB
2 /
BUYERID AUTHORID
---------- ----------
1 1
1 1
1 1
2 1
3 1
3 2
3 3
4 1
4 2
5 1
10 rows selected.
You can obatin the count of distinct authors for each buyer like so:
SCOTT@orcl_12.1.0.2.0> SELECT A.Lastname || ' ' || A.Firstname as Name,
2 COUNT (...)
3 FROM TableA A, TableB B
4 WHERE A.BuyerID = B.BuyerID
5 GROUP BY ...
6 /
NAME COUNT(DISTINCT(B.AUTHORID))
------------------------------- ---------------------------
Laura Ryan 1
Tanya Wayne 3
Bill Mike 1
Jane James 2
Pete Lone 1
5 rows selected.
You can obtain the maximum count of distinct authors like so:
SCOTT@orcl_12.1.0.2.0> SELECT ...
2 FROM TableB
3 GROUP BY ...
4 /
MAX(COUNT(DISTINCT(AUTHORID)))
------------------------------
3
1 row selected.
Then you need to put the two queries above together, in order to select the row from the first query having the count of distinct authors equal to the maximum count of distinct authors in the second query, like so:
SCOTT@orcl_12.1.0.2.0> SELECT A.Lastname || ' ' || A.Firstname as Name
2 FROM TableA A, TableB B
3 WHERE A.BuyerID = B.BuyerID
4 GROUP BY ...
5 HAVING ... =
6 (SELECT ...
7 FROM ...
8 GROUP BY ...)
9 /
NAME
-------------------------------
Tanya Wayne
1 row selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
