Inner Like Join?

Question

Inner Like Join?

adams.squared

SSCrazy

Points: 2675
More actions
March 10, 2009 at 11:30 am

#135485

I am trying to join two tables. The columns have to do with access codes. Table 1 defines the access code to a particular screen. Table 2 defines the users with the different access codes.
Table 1
Screen 1234-A
Screen 2345-B
Screen 3456-C
Table 2
User1-AB
User2-A
User3-C
User4-BAC
User5-CB
I want to create a join to show all the users who have access to Screen 1234. As I am trying to show, User1 and User 4 have multiple access rights and User 2 has access to only Screen 1234. How do I join the two tables on the access codes if the access codes in table two can have multiple codes.
Here is my actual script. Function code being the screen code.
select a.FunctionCode, a.FunctionDescription, a.MenuCode, a.AccessCodes, b.AccessCodes, b.UserName
from FS_FunctionCode a left join FS_UserAccess b on b.AccessCodes like ('%'+a.AccessCodes+'%')
order by a.MenuCode, a.FunctionCode
I was thinking of some kind of LIKE join, or maybe a variable, but I can't get my head around it. The result I get is a match up of all the users with one access code, but none with the multiple codes. I had one of those late evenings and early mornings with a new application last night.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Instead of doing the AB, ABC thing, I'd normalize it and have one per row. That'll make the whole thing much easier to work with.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

steveb. SSC-Forever Points: 46733 More actions · Answer 2

I have to agree with the comment above about normalistation, you will be in for a lot of issues down the track with this sort of design

Lowell SSC Guru Points: 323518 More actions · Answer 3

correct me if I'm wrong, but the character after the '-'(A,B etc) is the screen...so if you get that substring, you could check for the charindex on each user:

try joining on this instead and tell me if that works:

left join FS_UserAccess b on CHARINDEX(SUBSTRING(b.AccessCodes,CHARINDEX('-',b.AccessCodes),a.AccessCodes) > 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

adams.squared SSCrazy Points: 2675 More actions · Answer 4

I'll mess with the CHARINDEX. I think that is my best option. There isn't a - in the field, just the codes. As far as normalizing, that's not possible as this is coming from a control table created by the application, unless someone is willing to explain to me how to unconcanate 1-30 characters and I can dump it into a temp table.

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 5

Do you have a Numbers/Tally table? If so, then it can be normalized pretty easily.

select substring(MyCol, Number, 1)

from dbo.MyTable

inner join dbo.Numbers

on Number <= len(MyCol);

Something like that will split up a string pretty darn fast.

If you don't have a Numbers table, can still be done by building one on the fly in a CTE. Need to see how to do that?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

adams.squared SSCrazy Points: 2675 More actions · Answer 6

I got it with the CHARINDEX. As soon as I saw your post I knew that was the key, I just couldn't get it to work. Turned out the problem was that the table with the access code assigned to the page had 2 dozen or so spaces after the code. So except for the A to A matchups, nothing else was showing up until I did a rtrim on a.AccessCodes. So here is what the final was.

select a.FunctionCode, a.FunctionDescription, a.MenuCode, b.UserName

from FS_FunctionCode a left join FS_UserAccess b on (CHARINDEX(rtrim(a.AccessCodes),b.AccessCodes)) > 0

order by a.MenuCode, a.FunctionCode

Thanks for your help, Lowell.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Hi

Sure the code Lowell posted works fine for your specified problem. But the other guys will inform you that this kind of database-/table-desing may bring you into much more problems while development.

Maybe have a look to this link http://en.wikipedia.org/wiki/Database_normalization . I'm quiet sure there are also many articles on this page about "database normalization".

Greets

Flo

adams.squared SSCrazy Points: 2675 More actions · Answer 8

i am not designing a table or a database. the problem was not how the table was designed. the problem was how to use the tables that i have been given.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 9

[font="Verdana"]You could use substring and UNPIVOT to take the data from the form

User1-AB

to:

User1 A

User1 B

That would then simplify the join. I'd do the UNPIVOT as a CTE.

[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

adams.squared (3/10/2009)
i am not designing a table or a database. the problem was not how the table was designed. the problem was how to use the tables that i have been given.

True enough and you said so. Folks are just concerned enough to tell you anyway... you might even tell the dummies that designed the app about it... or, not. 😉

Shifting gears, even though I'm a Tally table lover, I believe that Lowell's good CHARINDEX code will probably beat it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)