query for join

5 pts.
Tags:
JOIN statement
SQL Server Query
SQL Server tables
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • msi77
    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}
    1,660 pointsBadges:
    report
  • Darryn
    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.
    765 pointsBadges:
    report
  • Denny Cherry
    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.
    65,995 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following