joining tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

joining tables

Author

Topic

Mugil
Starting Member

5 Posts

Posted - 2012-07-06 : 23:05:31

I have two tables.
Requirement table and skills description table.
In requirement table I have three columns soid(primary key),reqid(primary key),skillscode(foreign key).

in skills description table I have 2 columns skillscode(primary key),skillsdesc.

In requirement table, the skillscode values will be in this format...0001693-5-4, 0001798-2-3(single row will have multiple skillscode with comma separated.

in skillsdesc table, the table values will be like skillscode 0001693 skillsdesc java,skillscode 0001798 skillsdesc .net

I want to join the table skillsdesc with requirement table and show the corresponding skill description for the skillscode in requirement table.

very very complicated. whole team is struggling because of this. please help.

Mugil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 23:15:50

just use like condition in join

ie


SELECT columns
FROM skilldesc sd
INNER JOIN requirement r
ON r.skillscode LIKE sd.skillscode + '-%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mugil
Starting Member

5 Posts

Posted - 2012-07-06 : 23:24:06

hi visakh,

Thanks for your reply,
The problem is, in requirement table for a single soid n reqid , its having multiple skillscode separated by commas:-(

Mugil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-07 : 00:29:22

quote:
Originally posted by Mugil

hi visakh,

Thanks for your reply,
The problem is, in requirement table for a single soid n reqid , its having multiple skillscode separated by commas:-(

Mugil

even then its fine
use like


SELECT columns
FROM skilldesc sd
INNER JOIN requirement r
ON ',' + r.skillscode LIKE ',' + sd.skillscode + '-%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mugil
Starting Member

5 Posts

Posted - 2012-07-07 : 00:58:11

quote:
Originally posted by visakh16

quote:
Originally posted by Mugil

hi visakh,

Thanks for your reply,
The problem is, in requirement table for a single soid n reqid , its having multiple skillscode separated by commas:-(

Mugil

even then its fine
use like

SELECT columns
FROM skilldesc sd
INNER JOIN requirement r
ON ',' + r.skillscode LIKE ',' + sd.skillscode + '-%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

super, thank you:-) will try n get back wit result:-)

Mugil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-07 : 16:32:27

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mugil
Starting Member

5 Posts

Posted - 2012-07-08 : 23:05:07

Hi Visakhm,

Sorry:(It's not working...Let me make my question little clear...

Requirement Table

SO_ID REQ_ID SKILLCODE
0001 00011 0001693-5-4, 0001798-2-3
0002 00011 0001234-3-2,0003456-8-9,0004534-7-6
0003 00012 0001456-0-1

Skills Table

SKill Code Skill Desc
0001693 Java
0001798 .Net
0001234 Mainframe
0003456 Linux
0004534 Ajax
0001456 Perl

Result after Join:

SO_ID REQ_ID SKILLCODE Skill_Desc
0001 00011 0001693-5-4, 0001798-2-3 Java,.Net
0002 00011 0001234-3-2,0003456-8-9,0004534-7-6 Mainframe,linux,Ajax
0003 00012 0001456-0-1 Perl

Please Help...thanks in advance:)

Mugil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 00:17:43

[code]
;With CTE
AS
(
SELECT r.SO_ID,r.REQ_ID,r.SKILLCODE,s.[SkillDesc]
FROM skills s
INNER JOIN requirement r
ON ',' + r.skillcode LIKE ',' + s.skillcode + '-%'
)

SELECT c.SO_ID,c.REQ_ID,c.SKILLCODE ,
STUFF((SELECT ',' + [SkillDesc] FROM CTE
WHERE SO_ID = c.SO_ID
AND REQ_ID = c.REQ_ID
AND SKILLCODE = c.SKILLCODE
FOR XML PATH('')),1,1,'')
FROM CTE c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mugil
Starting Member

5 Posts

Posted - 2012-07-09 : 01:39:48

quote:

Originally posted by visakh16


;With CTE
AS
(
SELECT r.SO_ID,r.REQ_ID,r.SKILLCODE,s.[SkillDesc]
FROM skills s
INNER JOIN requirement r
ON ',' + r.skillcode LIKE ',' + s.skillcode + '-%'
)

SELECT c.SO_ID,c.REQ_ID,c.SKILLCODE ,
STUFF((SELECT ',' + [SkillDesc] FROM CTE 
       WHERE SO_ID = c.SO_ID
       AND REQ_ID = c.REQ_ID
       AND SKILLCODE = c.SKILLCODE
       FOR XML PATH('')),1,1,'')
FROM CTE c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you so much visakhm...it works well:)

Mugil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 09:51:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources