CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nida
Nida

Reputation: 1702

Issue in multiple table join operation

I have two tables tbLicence and tbLicenceTypes.

tbLicence has three fields AgentId LineOfAuthority, LicenceTypes

tbLicence contains data like

AgentId          LineOfAuthority                  LicenceTypes
.........      ....................               ................ 
 287       Bail Bond,Casualty,Dental Services         1
 287       Casualty,Industrial Fire,Life              4
 287       Industrial Fire,Life                       4

tbLicenceTypes has two feilds LicenceTypes, LicenceTypesX

tbLicenceTypes contains data like

 LicenceTypes    LicenceTypesX   
 ............    .............
     1           Producer
     2          Sales Representative
     3          Insurance Agent
     4          Title Agent

Now I want to fetch all Licence Types assigned to Casualty. I meant AgentId is 287 and Line of Authority is casualty... The expected output should be like this

LicenceTypes    LicenceTypesX
...........    ...............
      1          Producer
      4         Title Agent

I have tried below lines of code... It doesn't work 

;WITH cte AS (
SELECT 
    AgentId,
    CAST('<r>' + REPLACE(REPLACE(LineOfAuthority,'&','&amp;'), ',', '</r>  <r>') + '</r>' AS XML) AS LineOfAuthoritys
FROM tbLicence
 )
,FinalList AS (
SELECT 
AgentId,
RTRIM(LTRIM (PTable.PColumn.value('.', 'VARCHAR(MAX)'))) AS LineOfAuthoritys
FROM cte
CROSS APPLY LineOfAuthoritys.nodes('//r') AS PTable(PColumn) 
)
SELECT DISTINCT 
   T.LicenceTypes AS LicenceTypes ,
   F.LineOfAuthoritys AS LicenceTypesX  
  FROM FinalList F
  CROSS APPLY (SELECT LicenceTypes FROM tbLicenceTypes TP WHERE            TP.LicenceTypesX = f.LineOfAuthoritys) AS T
  WHERE F.LineOfAuthoritys = @LineOfAuthority
  AND F.AgentId = @AgentId
  ORDER BY T ASC

Please help me!!!

Upvotes: 0

Views: 60

Answers (2)

Sarath Subramanian
Sarath Subramanian

Reputation: 21311

SAMPLE TABLE

CREATE TABLE #tbLicence (AgentId INT, LineOfAuthority VARCHAR(200), LicenceTypes INT)

INSERT INTO #tbLicence
SELECT    287,  'Bail Bond,Casualty,Dental Services',1
UNION ALL
SELECT     287,  'Casualty,Industrial Fire,Life',4
UNION ALL
SELECT     287,  'Industrial Fire,Life',4

CREATE TABLE #tbLicenceTypes(LicenceTypes INT, LicenceTypesX VARCHAR(200))

INSERT INTO #tbLicenceTypes
SELECT    1,  'Producer'
UNION ALL
SELECT     2,  'Sales Representative'
UNION ALL
SELECT     3,  'Insurance Agent'
UNION ALL
SELECT     4,  'Title Agent'

QUERY

;WITH cte AS 
(
    SELECT 
    AgentId,LicenceTypes,
    CAST('<r>' + REPLACE(REPLACE(LineOfAuthority,'&','&amp;'), ',', '</r>  <r>') + '</r>' AS XML) AS LineOfAuthoritys
    FROM tbLicence
)
,FinalList AS (
    SELECT 
    AgentId,LicenceTypes,
    RTRIM(LTRIM (PTable.PColumn.value('.', 'VARCHAR(MAX)'))) AS LineOfAuthoritys
    FROM cte
    CROSS APPLY LineOfAuthoritys.nodes('//r') AS PTable(PColumn) 
)  
SELECT DISTINCT 
   L.LicenceTypes AS LicenceTypes ,
   L.LicenceTypesX AS LicenceTypesX   
  FROM FinalList F
  JOIN tbLicenceTypes L ON F.LicenceTypes=L.LicenceTypes 
  WHERE F.LineOfAuthoritys = 'Casualty'
  AND F.AgentId = 287
  ORDER BY L.LicenceTypesX ASC

Upvotes: 1

Matt
Matt

Reputation: 15091

LineOfAuthority should be redesigned as many-to-many relation using additional tables. When it is done, the query would be an easy thing to do. If you don't know how to make a many-to-many relation, you'd better read some good book on RDBMS first.

Upvotes: 0

Related Questions