Home » SQL & PL/SQL » SQL & PL/SQL » Extract the most data after joining 3 tables (Oracle SQL)
icon5.gif  Extract the most data after joining 3 tables [message #652509] Sat, 11 June 2016 03:17 Go to next message
misy
Messages: 5
Registered: June 2016
Junior Member
TableA
BuyerID
LastName
FirstName
Date

TableB
BuyerID
AuthorID

TableC
AuthorID
LastName
FirstName

SELECT DISTINCT A.Lastname||''||A.Firstname as Name
FROM TableA A,TableB  B,TableC C 
WHERE A.BuyerID = B.BuyerID
AND C.AuthorID =B.AuthorID;

Output
Name
1. Bill Mike
2. Laura Ryan
3. Tanya Wayne
4. Jane James
5. Pete Lone

Question.
I want to show only Tanye Wayne is the most buyer to all the author.
Re: Extract the most data after joining 3 tables [message #652512 is a reply to message #652509] Sat, 11 June 2016 04:27 Go to previous messageGo to next message
John Watson
Messages: 8580
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 #652513 is a reply to message #652512] Sat, 11 June 2016 04:38 Go to previous messageGo to next message
misy
Messages: 5
Registered: June 2016
Junior Member
Pull out Tanya Wayne as she is the only buyer that consist all the author.
Sorry I dont get your statement "describing the GROUP BY ... HAVING ... clause. Your answer is probably in there somewhere."
Re: Extract the most data after joining 3 tables [message #652514 is a reply to message #652513] Sat, 11 June 2016 04:40 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
I still have no idea what you mean.

GROUP BY and HAVING are keywords you can use in a SELECT statement. You need to read up on how to use them.
Re: Extract the most data after joining 3 tables [message #652515 is a reply to message #652514] Sat, 11 June 2016 04:51 Go to previous messageGo to next message
misy
Messages: 5
Registered: June 2016
Junior Member
YES I know what is meant by group by and having clause. These command doesn't help me extract the most data when merge 3 tables. It shows all the data which I don't really want it.
Re: Extract the most data after joining 3 tables [message #652516 is a reply to message #652515] Sat, 11 June 2016 05:01 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
You have to build up your query slowly. What do you get when you use a GROUP BY ?
Re: Extract the most data after joining 3 tables [message #652517 is a reply to message #652509] Sat, 11 June 2016 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Fri, 10 June 2016 02:26
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.


This also applies to result chart and table description.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Sat, 11 June 2016 05:06]

Report message to a moderator

Re: Extract the most data after joining 3 tables [message #652520 is a reply to message #652509] Sat, 11 June 2016 07:30 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 8922
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.
Previous Topic: Query that gives many different values
Next Topic: order of geting value from sequence by trigger
Goto Forum:
  


Current Time: Thu Jul 29 23:24:35 CDT 2021