Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Question on SQL outer join

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Roshnii

New User


Joined: 30 Sep 2008
Posts: 45
Location: bangalore

PostPosted: Fri Jun 26, 2009 1:34 am    Post subject: Question on SQL outer join
Reply with quote

I have two tables which have data like this :
Code:

table A

Member ID(char3)(primary key)  subject(varchar 20)
123                                         english                 
123                                         maths                       
123                                         chemistry             
123                                         language               
123                                         literature               

Code:

table b
subject_taken(primary key) marks    teacher

english                   85         robin
maths                      95         smith       

I want all the values from table A and the values from table B which has a matching subject in table A.
Also in the output, the rows from table B should first appear in the ascending order and then the rows of table B in ascending order. So my resulting output should look like this:

subject

english
maths
chemistry
language
literature


I have written a query as follows:

SELECT

SUBJECT,
SUBJECT_TAKEN

FROM TABLE A LEFT OUTER JOIN TABLE B


ON SUBJECT = SUBJECT_TAKEN

WHERE MEMBER_ID = '123'

ORDER BY SUBJECT_TAKEN;

BUT i am not getting the desired output. where am i going wrong?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 26, 2009 2:37 am    Post subject:
Reply with quote

Hello,

Quote:
BUT i am not getting the desired output.
Do not post "it didn't work". . . You need to show what did happen.

You select 2 columns, but your desired output only shows 1 icon_confused.gif
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Fri Jun 26, 2009 6:46 pm    Post subject: Reply to: question on outer join
Reply with quote

Hi,
If you need all the column from table A in sorting order ?????
why do you need to join it with table B ???????
Directly select it from table A

Or your requirement is something different.
Please ellobrate your question

Hope this helps
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Sat Jun 27, 2009 12:47 am    Post subject: Reply to: question on outer join
Reply with quote

Hi Roshnii

Quote:
Also in the output, the rows from table B should first appear in the ascending order and then the rows of table B in ascending order.


Quote:
So my resulting output should look like this:

subject

english
maths
chemistry
language
literature


Quote:
I have written a query as follows:

SELECT

SUBJECT,
SUBJECT_TAKEN

FROM TABLE A LEFT OUTER JOIN TABLE B


ON SUBJECT = SUBJECT_TAKEN

WHERE MEMBER_ID = '123'

ORDER BY SUBJECT_TAKEN;



Total confusion. Resulting output shows one column and you have included 2 columns in the SQL.
Really we can only assume your requirements.
As per my assumption, you need two columns to be displayed based on the values of the column subject in table A and subject_taken in table B.
You try this SQL for the assumption if my assumption is correct.

Code:
 SELECT SUBJECT, SUBJECT_TAKEN FROM A LEFT OUTER JOIN B ON  SUBJECT = SUBJECT_TAKEN;


Post a replay if my assumption is wrong and the output of the query is not the required one.

Regards
Raghu
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts question about dfsms management clas... Hervey Martinez IBM Tools 0 Tue Oct 10, 2017 6:19 pm
No new posts Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts QUESTION: RETURN CHANNEL pahiker CA Products 21 Thu Apr 20, 2017 12:31 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us