Combining SELECT query, JOIN table with SUBSTRING

Question

Combining SELECT query, JOIN table with SUBSTRING

phina_el

SSC-Addicted

Points: 408
More actions
July 6, 2010 at 10:33 pm

#237304

Dear All,
I want to display data USER LOGIN INFORMATION and ACCESS TYPE from 3 table:
A. Table TblProgram
|Byte|Program_Name|
| 1|Rohs Web|
| 2|Bea Cukai 4.0|
| 3|Damage Cargo|
| 4|DDPR|
| 5|Production Schedule Maintenance|
| 6|Standard Time|
| 7|Supplier Arrival|
B. TABLE TblProgramDetail
|Byte|Type|Access_Description|
|1|0|Not Accessible|
|1|1|Inquiry Only|
|1|2|Upload File VC97|
|1|3|Update Status Rohs and Upload Attachment|
|1|4|Admin|
|2|0|Not Accessible|
|2|1|Inquiry Only|
|2|2|Admin|
|3|0|Not Accessible|
|3|1|Inquiry Only|
|3|2|Admin|
|4|0|Not Accessible|
|4|1|Inquiry Only|
|4|2|Admin|
C. Table TblUserAccess
User_IDUser_NamePasswordDeptAccess_Type
EDP4 EMILY 12345EDP4102000000
ACC1 AFIF 23456ACC1220000000
How to make a query that combines JOIN table with substrings operation,
and resulting in the following data:
RESULT:
|User_ID|Program_Name|Access_Description|Byte |Type|
| EDP4|Rohs Web |Admin |1 |4 |
| EDP4|Bea Cukai 4.0|Inquiry Only |2 |1 |
| EDP4|DDPR |Inquiry Only |4 |2 |
| ACC1|Rohs Web |Inquiry Only |1 | 1 |
| ACC1|Bea Cukai 4.0|Admin |2 | 2 |
| ACC1|Damage Cargo |Admin |3 | 2 |
Thank You....
phina_el

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

1. Please click the link in my signature, it will explain how t post your question to get timely answers

2. Also, could you please explain what rules for joining betwen your tables you want. I can see it should do it based on Access Type, but what of it should be joined to what? I can't see it clear from estimated results

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

phina_el SSC-Addicted Points: 408 More actions · Answer 2

Hi There,

Thank You for your suggestion.

I have three tables that identify the user's login information on an application.

There is TblProgram,TblProgramDetail,TblUserAccess.

CREATE TABLE [TblProgram](

[Byte] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ProgramName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

--Data of Table TblProgram:

INSERT INTO [TblProgram]

([Byte]

,[ProgramName])

SELECT 1, 'Rohs Web' UNION ALL

SELECT 2,'Bea Cukai 4.0' UNION ALL

SELECT 3,'Damage Cargo Report' UNION ALL

SELECT 4,'Delivery Data Problem Report' UNION ALL

SELECT 5,'Produc tion Schedule Maintenance' UNION ALL

SELECT 6,'Standard Time' UNION ALL

SELECT 7,'Supplier Arrival'

CREATE TABLE [dbo].[TblProgramDetail](

[Byte] [int] NOT NULL,

[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Access_Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

--Data of Table TblProgramDetail:

INSERT INTO [TblProgramDetail]

([Byte]

,[Type]

,[Access_Description])

SELECT 1, 0, 'Not Accessible' UNION ALL

SELECT 1, 1, 'Inquiry Only' UNION ALL

SELECT 1, 2, 'Upload File VC97' UNION ALL

SELECT 1, 3, 'Update Status Rohs and Upload Attachment' UNION ALL

SELECT 1, 4, 'Admin' UNION ALL

SELECT 2, 0, 'Not Accessible' UNION ALL

SELECT 2, 1, 'Inquiry Only' UNION ALL

SELECT 2, 2, 'Admin' UNION ALL

SELECT 3, 0, 'Not Accessible' UNION ALL

SELECT 3, 1, 'Inquiry Only' UNION ALL

SELECT 3, 2, 'Admin' UNION ALL

SELECT 4, 0, 'Not Accessible' UNION ALL

SELECT 4, 1, 'Inquiry Only' UNION ALL

SELECT 4, 2, 'Admin'

CREATE TABLE [dbo].[ TblUserAccess](

[UserId] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Password] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Dept] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[UserType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

--Data of Table TblUserAccess:

INSERT INTO [TblUserAccess]

([UserId]

,[UserName]

,[Password]

,[Dept]

,[UserType])

SELECT 'EDP4', 'EMILY','12345', 'EDP','4102000000' UNION ALL

SELECT 'ACC1', 'AFIF','23456', 'ACC','1220000000'

I need a summary of the user login information on application,

eg user "EDP4" he can access the application "RoHS Web" in which access rights are as "ADMIN",

(which is obtained from TblUserAccess field "Access_Type" is "4102000000". Where the first byte is "4" in TblProgram its mean "Rohs Web", which when in combination with TblProgramDetail it means that the Access Description is "ADMIN").

Note: then the second byte is "1", the third byte is "0", the fourth byte is "2", .... Tenth byte is "0"

Can any one suggest me how can data like this:

| EDP4 |Rohs Web |Admin |1 |4 |

| EDP4 |Bea Cukai 4.0 |Inquiry Only |2 |1 |

| EDP4 |DDPR |Inquiry Only |4 |2 |

| ACC1 |Rohs Web |Inquiry Only |1 | 1 |

| ACC1 |Bea Cukai 4.0 |Admin |2 | 2 |

| ACC1 |Damage Cargo |Admin |3 | 2 |

Thank You.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ugg...i've inherited a couple of situations like this, where a field needs a Decoder Ring to parse out the secret values contained in the field...it's a pain since it doesn't follow a proper foreign key relationship where you'd simply join on ten tables to decode the 10 values...chances are the ten values represented by the fields are not even in tables, but are just "known"

what you want to do is build a view that parse that field out for you, and use THAT to join to your other data.

something like this...i've done the first field based on your data to show the technique but the details need to be expanded quite a bit:

--sample results:

UserId UserName Password Dept UserType ProgramName Access_Description

EDP4 EMILY 12345 EDP 4102000000 Rohs Web Admin

ACC1 AFIF 23456 ACC 1220000000 Bea Cukai 4.0 Inquiry Only

create view DecoderRing

As

--the base filed UserType is defined as VARCHAR(25)...

--is there 25 "bytes" in the field? do you assume left to right, or is it always 10 chars but accidentally defined larger?

--what if the value were just '4200'?

select

UserId,

UserName,

Password,

Dept,

UserType,

[TblProgram].[ProgramName],

[TblProgramDetail].[Access_Description],

SUBSTRING(UserType,2,1) As Byte2,

SUBSTRING(UserType,3,1) As Byte3,

SUBSTRING(UserType,4,1) As Byte4,

SUBSTRING(UserType,5,1) As Byte5,

SUBSTRING(UserType,6,1) As Byte6,

SUBSTRING(UserType,7,1) As Byte7,

SUBSTRING(UserType,8,1) As Byte8,

SUBSTRING(UserType,9,1) As Byte9,

SUBSTRING(UserType,10,1) As Byte10,

SUBSTRING(UserType,1,1) As [Byte] ,

SUBSTRING(UserType,2,1) As [Type]

from [TblUserAccess]

LEFT OUTER JOIN [TblProgram]

ON CONVERT(INT,SUBSTRING([TblUserAccess].UserType,2,1)) = [TblProgram].[Byte]

LEFT OUTER JOIN [TblProgramDetail]

ON CONVERT(INT,SUBSTRING([TblUserAccess].UserType,1,1)) = [TblProgramDetail].[Type]

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!