query for join
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
Software/Hardware used:
Software/Hardware used:
ASKED:
June 1, 2009 12:50 PM
UPDATED:
June 4, 2009 8:11 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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}
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.
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.