query for join
Home > IT Answers > SQL Server > query for join
Rampradeep
5 pts.
0
Q:
query for join
SQL Server Query, SQL Server tables, JOIN statement
i have 2 tables unittypes,rules

unittypes has unitid and description

unitid description

A1 description1
A2 description2
A3 description3


rules has

id unitid
1 A1,A2

2 A1,A3


i am trying to get it to get a resultset like


1 description1,description2
2 description1,description3

the sql i wrote was

select r.unitid ,(select distinct description from unittypes where
unittypes.unitid in ( (''''+ REPLACE(r.unitid,',',''',''')+'''') ) FOR XML PATH('') ) o
from rules r

it always returns null, can anyone please help me out with the query
ASKED: Jun 1 2009  12:50 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Kccrosser
1850 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Assuming that your Rules table always has TWO UnitId values, separated by a comma, then I would suggest using a function to return the two strings, e.g.:

create function TwoStrings (
@inID int
)
return varchar(255)
begin
declare @outString varchar(255)
declare @temp varchar(128)
declare @unitID1 varchar(32)
declare @unitID2 varchar(32)
set @temp = select UnitID from Rules where id = @inID
set @unitID1 = substring(@temp, 1, 2)
set @unitID2 = substring(@temp, 3, 2)
set @outString = (select Description from UnitTypes where UnitID = @unitID1 +
', ' + select Description from UnitTypes where UnitID = @unitID2)
return @outString
end

Then, the result would be obtained from:

select Id, TwoStrings(Id) from Rules

Now - this code snippet violates a lot of my rules about handling unexpected conditions, testing for nulls, etc. It will only work when ALL of the input rows have exactly "xx,yy" in the UnitID column - no white space, no single values, etc.

Proper code would test the inputs and ensure the unit id values were valid, handle unit ids that are longer (or shorter?) than 2 characters, watch for white space, etc. Note that if any records contain only one unit id, that row will return a null string in the above code, as the string concatenation will return null if any of the parts are null.

A much better data model would be to have separate rows for each pairing of UnitID and ID in the Rules table. Embedding values to use for joins or indexing in the middle of a string is not a recommended procedure.
Last Answered: Jun 3 2009  0:09 AM GMT by Kccrosser   1850 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Msi77   800 pts.  |   Jun 3 2009  6:35AM GMT

I think you need to change the design of Rules table, so it is not in 1NF.
You’ll have no issues with join if this table will be

id unitid
1 A1
1 A2
2 A1
2 A3

PK={id, unitid}

 

Darryn   370 pts.  |   Jun 3 2009  10:37AM GMT

Or, update your query (should work, but haven’t tested).

select r.unitid ,(select distinct description from unittypes where
unittypes.unitid in ( (””+ REPLACE(r.unitid,’,',””+’,'+””)+””) ) FOR XML PATH(”) ) o
from rules r

Agree with advice from Msi77 though, it would be better to change the design.

 

mrdenny   46795 pts.  |   Jun 4 2009  8:11PM GMT

A table redesign is the way to go here. This is one of the many reasons that you don’t store more than one value in a single field.

 
0