| Author |
 Topic  |
|
|
Kimi86
Yak Posting Veteran
54 Posts |
 Posted - 07/12/2012 : 07:13:23
|
I have a senario, But that 2 complicated so i have come up with an easier expale for teh same problem.
Table1 : Marks_BreakUp
Student_ID Subject Marks Grad
1000 MATH1 5 B
1000 MATH2 6 B
1000 ENG1 5 B
1000 ENG2 2 C
1001 MATH1 1 D
1001 MATH2 8 A
1002 MATH1 9 A
1002 MATH2 4 C
Table2: Tot_Marks
Student_ID SubjectAreas Tol_Marks Tot_Grade
1000 MATH 11 B
1000 ENG 7 C
1001 MATH 9 C
1002 MATH 13 A
*There can be other Subjects as well
* I cannot alter any of the above 2 tables
* Need to Map as follows
* There is no table as such that maps these values
MATH1 - MATH
MATH2 - MATH
ENG1 - ENG
ENG2 - ENG
RESULT VIEW should look like this:
Student_ID Subject Marks Grad Tol_Marks Tot_Grade
1000 MATH1 5 B 11 B
1000 MATH2 6 B 11 B
1000 ENG1 5 B 7 C
1000 ENG2 2 C 7 C
1001 MATH1 1 D 9 C
1001 MATH2 8 A 9 C
1002 MATH1 9 A 13 A
1002 MATH2 4 C 13 A
SCRIPTS to create tables are below:
Create TAble Marks_BreakUp
(
Student_ID int,
Subject varchar(10),
Marks int,
Grad Char)
Insert into #T1 values(1000,'MATH1',5,'B')
Insert into #T1 values(1000,'MATH2',6,'B')
Insert into #T1 values(1000,'ENG1',5,'B')
Insert into #T1 values(1000,'ENG2',2,'C')
Insert into #T1 values(1001,'MATH1',1,'D')
Insert into #T1 values(1001,'MATH2',8,'A')
Insert into #T1 values(1002,'MATH1',9,'A')
Insert into #T1 values(1002,'MATH2',4,'C')
Create TAble Tot_Marks
(
Student_ID int,
SubjectAreas varchar(10),
Tot_Marks int,
Tot_Grad Char)
Insert into #T1 values(1000,'MATH',11,'B')
Insert into #T1 values(1000,'ENG',7,'C')
Insert into #T1 values(1001,'MATH',9,'C')
Insert into #T1 values(1002,'MATH',13,'A')
-- Please Note that I am cretaing a view here ...
|
|
|
lionofdezert
Aged Yak Warrior
Pakistan
864 Posts |
Posted - 07/12/2012 : 07:24:10
|
SELECT Marks_BreakUp.Student_ID,Marks_BreakUp.Subject,
Marks_BreakUp.Marks,
Marks_BreakUp.Grad,Tot_Marks.Tot_Grad,Tot_Marks.Tot_Marks FROM
Marks_BreakUp INNER JOIN Tot_Marks
ON Marks_BreakUp.Student_ID = Tot_Marks.Student_ID
AND SOUNDEX(Marks_BreakUp.[Subject]) = SOUNDEX(Tot_Marks.[SubjectAreas])
--------------------------
http://connectsql.blogspot.com/ |
 |
|
|
saran_d28
Starting Member
India
36 Posts |
Posted - 07/12/2012 : 07:34:23
|
Try this
Select
Marks_BreakUp.*, Tot_Marks.tot_marks, tot_marks.tot_grad
FROM Marks_BreakUp
Left Join Tot_Marks
ON LEFT(Marks_BreakUp.subject, LEN(Marks_Breakup.subject)-1) = Tot_Marks.subjectAreas
AND Marks_BreakUp.Student_ID = Tot_Marks.Student_ID |
|
|
|
Kimi86
Yak Posting Veteran
54 Posts |
Posted - 07/12/2012 : 07:34:40
|
| Thanks for the reply. But i cannot really be sure if these columns are going to sound similar..Id there any way of doing without SOUNDx |
|
|
|
Kimi86
Yak Posting Veteran
54 Posts |
Posted - 07/12/2012 : 07:40:19
|
in my original expalple the mapping looks like this
'B205XPP' - Terrerism
'PO05039' - Terrerism
These are 2 covers related to terrorism, As you can see they do not sound same or have common characters that i can use. Are there any other ways. If not what options do i have???
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 07/12/2012 : 11:40:14
|
I'm not sure I fully understand the requirement. But, maybe you can use a LIKE comparison:Marks_BreakUp.Subject LIKE Tot_Marks.SubjectArea + '%' |
|
|
| |
Topic |
|
|
|
How to do 2-1 mapping through joins??
