Select Names Based In Ids
-
i've a query in which i want to select names based on ids using join. i get the names where there is only one id in column i get corresponding name from other table. but where there are multiple comma separated ids in columns i'm not able to get names corresponding to that comma separated ids.
my query
SELECT CPA_00_01.Id_Num, CPA_00_01.Auditees , Employee_00_01.First_Name FROM dbo.CPA_00_01 left JOIN dbo.Employee_00_01 on dbo.CPA_00_01.Auditees = CAST(dbo.Employee_00_01.Id_Num AS VARCHAR(20))result from query is this
i want to select two comma separated names where the name value is NULL
-
Quick question, can you post the DDL (create table) for the tables, sample data as an insert statement and the expected result set. This problem is trivial but in order to provide a proper solution, one needs the sample setup and data.
😎
Further on the problem, how many unique IDs would you have? How many would be in the Auditees column at the most? Is there an option of normalizing the schema and eliminating the concatenated values?
-
Eirikur Eiriksson (8/1/2016)
Quick question, can you post the DDL (create table) for the tables, sample data as an insert statement and the expected result set. This problem is trivial but in order to provide a proper solution, one needs the sample setup and data.😎
Further on the problem, how many unique IDs would you have? How many would be in the Auditees column at the most? Is there an option of normalizing the schema and eliminating the concatenated values?
infact there is a multiple select list. many auditees can be selected in it, and that's auditees ID's save in Auditees column as 9,14 if two auditees are selected.
All are unique ids in Auditees column in one row. one auditee cannot be selected twice in the multiple select list.
-
Messi (8/1/2016)
Eirikur Eiriksson (8/1/2016)
Quick question, can you post the DDL (create table) for the tables, sample data as an insert statement and the expected result set. This problem is trivial but in order to provide a proper solution, one needs the sample setup and data.😎
Further on the problem, how many unique IDs would you have? How many would be in the Auditees column at the most? Is there an option of normalizing the schema and eliminating the concatenated values?
infact there is a multiple select list. many auditees can be selected in it, and that's auditees ID's save in Auditees column as 9,14 if two auditees are selected.
All are unique ids in Auditees column in one row. one auditee cannot be selected twice in the multiple select list.
Did you understand the questions I'm asking? There are many ways of solving this problem and arguably the best option is to change the schema with the introduction of a link table (1-N). The performance and the applicability of other solutions are highly dependent on various properties of the data set such as cardinality etc.
😎
-
To do what you're asking for, you need to split the values and store them in individual rows. Of course, you can split them and join them again in a single query, but that's going to be slow. Normalizing your database should be part of your plans to solve this.
-
Luis Cazares (8/1/2016)
To do what you're asking for, you need to split the values and store them in individual rows. Of course, you can split them and join them again in a single query, but that's going to be slow. Normalizing your database should be part of your plans to solve this.yes i solved my problem in this way.. some one else designed database so i told him about it.. it's not a good way to design database:cool:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply