How to do 2-1 mapping through joins?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to do 2-1 mapping through joins??

Author

Topic

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2012-07-12 : 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

885 Posts

Posted - 2012-07-12 : 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

36 Posts

Posted - 2012-07-12 : 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

79 Posts

Posted - 2012-07-12 : 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

79 Posts

Posted - 2012-07-12 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 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 + '%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources