View previous topic :: View next topic
|
Author |
Message |
Roshnii
New User
Joined: 30 Sep 2008 Posts: 45 Location: bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
Back to top |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008 Posts: 91 Location: banglore
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
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 |
|
|
|