How to fetch from column where each values are seperated by ","?

Question

I have two tables in the database tbLicence and tbJurisdiction tbJurisdiction has two feilds

- Jurisdiction as PK
- JurisdictionX as Jurisdiction Name

tbLicence has three feilds

- Licence as PK
- AgentId 
- Jurisdiction

Let's us say

tbJurisdiction contains values as

- 1 - Alabama
- 2 - Florida
- 3 - New York
- 4 - Mexico
- 5 - New Jersey

tbLicence contains values as

- 1 - 224 - Florida, New York
- 2 - 304 - New Jersey, Alabama

Now I want to fetch Jurisdiction and JurisdictionX on the basis of AgentId let us say 224

I want the final output should be like

- 2 - Florida
- 3 - New York

I have tried the below query

   SELECT L.Jurisdiction,
   L.JurisdictionX
   FROM   tbJurisdiction L
   Join tbLicence P on
    Jurisdiction
   Where P.AgentId =@AgentId;

Its not working

Please help me !!!

Sir I am sure that the design is very poor but I have not designed the table... it was designed by some other devolepers — Nida, Jan 13 '15 at 06:20
@nida i can understand what you meant. but using this csv data you can be under huge problems later in the project. may be some how you will manage to make all business logic into the query using this 1NF, but later when user requirement grow you might find it difficult to handle. i am always look all problem with optimization angle and trust me you will be in deep deep trouble if you continue this architecture, optimizer wont able to guess what data it gonna handle as answer is closed and i only can comment. i say please google: and more importantly i would suggest learn about normal forms — Neeraj Prasad Sharma, Jan 13 '15 at 06:40

0 Answers0