Home » SQL & PL/SQL » SQL & PL/SQL » Join Tables (Oracle 8i)
Join Tables [message #445371] Mon, 01 March 2010 05:02 Go to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Hi,

I have 2 tables as shown below. I have to join those tables and get data as in table 3. Condition is I have to get sum of scores for each student where category is 1 and active is Y.

Table1:
col1 col2 category
A 10 1
A 10 2
B 10 1
B 20 2
C 10 1
D 20 1
J 30 1

Table2:
colA colB Active
A 10 Y
A 20 N
B 30 Y
B 40 N
Z 35 Y


Table3:
STUDENT SCORE
A 20
B 40
C 10
D 20
J 30
Z 35

Please help me to write query to show student name and sum of his score where category is 1 and active is Y. I am using Oracle 8i.

Harsha.

[Updated on: Mon, 01 March 2010 05:06]

Report message to a moderator

Re: Join Tables [message #445372 is a reply to message #445371] Mon, 01 March 2010 05:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Seeing as this is obviously homework, here's some pointers, but the SQL is up to you:

1) Write a query using UNION ALL to return the required data from the two tables
2) Use the query from 1 as the source for a query that groups and sums groups the data
Re: Join Tables [message #445373 is a reply to message #445372] Mon, 01 March 2010 05:13 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Hi JRowbottom,

Thanks for the reply.
But I didn't understood this. "Use the query from 1 as the source for a query that groups and sums groups the data". Also I have tried many ways. So let me know how to write query for the same.

Harsha.
Re: Join Tables [message #445379 is a reply to message #445373] Mon, 01 March 2010 05:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Point 2 is about using the query from 1) as an inline view - ie
  SELECT <column list 1>
  FROM   (SELECT <column list 2>
          FROM   <some tables>
          WHERE  <some conditions>)


Show us what you've tried - I'm not about to do your homework for you.
Re: Join Tables [message #445381 is a reply to message #445379] Mon, 01 March 2010 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And your first step will be to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Also if you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel
Re: Join Tables [message #445384 is a reply to message #445381] Mon, 01 March 2010 06:01 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
thank you very much Michel for your suggestions.
Re: Join Tables [message #445466 is a reply to message #445384] Tue, 02 March 2010 00:22 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Hi Michel,

Below are the scripts to create table1 and table2. Also I am really sorry for not giving this in my first thread.

And also I have forgot add one point in my first thread. That is in Second column there is a column called colD which will store points. that is point column is not same as scores.

Condition is I have to get sum of scores and sum of points for each student where category is 1 and active is Y.

Below is the script to create table1

CREATE TABLE TABLE1
(
  COLA      VARCHAR2(100),
  COLB      NUMBER,
  CATEGORY  NUMBER
)



INSERT INTO TABLE1(Cola,ColB,Category) values('A',10,1)
INSERT INTO TABLE1(Cola,ColB,Category) values('A',20,1)
INSERT INTO TABLE1(Cola,ColB,Category) values('A',20,2)
INSERT INTO TABLE1(Cola,ColB,Category) values('B',10,1)
INSERT INTO TABLE1(Cola,ColB,Category) values('C',20,2)
INSERT INTO TABLE1(Cola,ColB,Category) values('C',30,1)
INSERT INTO TABLE1(Cola,ColB,Category) values('Z',40,1)
INSERT INTO TABLE1(Cola,ColB,Category) values('Z',20,1)



Below is the script to create table2.

CREATE TABLE TABLE2
(
  COLC    VARCHAR2(100),
  COLD    INTEGER,
  ACTIVE  CHAR(1)
)

INSERT INTO TABLE2(Colc,ColD,Active) values('A',1,'Y')
INSERT INTO TABLE2(Colc,ColD,Active) values('A',2,'Y')
INSERT INTO TABLE2(Colc,ColD,Active) values('A',2,'N')
INSERT INTO TABLE2(Colc,ColD,Active) values('B',1,'Y')
INSERT INTO TABLE2(Colc,ColD,Active) values('Y',4,'Y')
INSERT INTO TABLE2(Colc,ColD,Active) values('D',3,'Y')
INSERT INTO TABLE2(Colc,ColD,Active) values('D',2,'N')



Please tell what query I need to write so that I will get sum of scores and points for all students as in below table table3. I think it is something like full outer join in Sql. Thank you very much in advance.


Table3
STUDENT SCORE 	POINTS
A 	30 	3
B 	30 	1
C 	30 	0
D 	0 	3
Y 	0 	4
Z 	60 	0

Harsha.

[Updated on: Tue, 02 March 2010 00:26] by Moderator

Report message to a moderator

Re: Join Tables [message #445469 is a reply to message #445466] Tue, 02 March 2010 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And also I have forgot add one point in my first thread.

This is why builting a test case also helps: it allows to fix the requirements.

You are talking about STUDENTS, SCORE, POINTS but your example only shows COLA, COLB, COLC and COLD.

You can build your query in 3 steps:
- Assuming COLA and COLC are STUDENTS, you can join both tables on these columns.
- Then you can restrict this for the CATEGORY and ACTIVE state you want.
- Then you make the SUM of SCORE/COLB and POINTS/COLD columns grouping by STUDENTS/COLA.

As you have students with score but no point and vice versa, the join in first step should be a full outer join.
Try this and come back with your tries if you can't get it.

Regards
Michel
Re: Join Tables [message #445542 is a reply to message #445371] Tue, 02 March 2010 07:14 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I do not wish to be rude, but so far OP has only demonstrated the ability to CUT/PASTE.

As was previously asked for, please show use what you have written so far in terms of your own SQL.

Additionally, I would ask, do you not have a book about SQL? If not then you need to get one and do some reading. Even if we give you an answer for this question, if you have no learning material, it only means we will see you next week with another question.

I mention the above because your question is really a basic question that is answered in just about any book on SQL. If you can't find one then head to any bookstore and ask for SQL FOR DUMMIES. Yes, there is a real book by this name. I am not trying to insult you in any way.

My mention of this book is not an endoursement of this book or the series of books by similar name. Just one example. A quick google will show many appropriate books and just heading to your favorite book store and going to the programming sections will yeild between 5 to 15 such books.

Bottom line, 90 minutes of reading will get you your answer and also send you on your way to learning SQL.

As an aside, if you cannot afford the 24$ for this book then go to your local library. They will have it or something else just as useful. And do not worry about the age of the book. SQL has been around for 25 years and the basics have not changed at all in that time. So any old book on the subject will get you the introduction you need. If you are not able to get to a library for whatever reason, then there are also many good SQL Tutorials online. Again a google will find them.

Good luck, Kevin.

[Updated on: Tue, 02 March 2010 07:19]

Report message to a moderator

Previous Topic: CONTAINS purpose
Next Topic: model-clause with interdependend parameters
Goto Forum:
  


Current Time: Sun Dec 11 08:09:12 CST 2016

Total time taken to generate the page: 0.10260 seconds