Getting no rows here!!!!!
-
Hello friends!!!
my table structure is like this..
create table test1 (id varchar(10), name varchar(10) default 'ABCD')
insert into test1 (id,name) values ('1,7',default)
insert into test1 (id,name) values ('2,5,8',default)
insert into test1 (id,name) values ('6,4',default)
insert into test1 (id,name) values ('3,10',default)
create table test2 (id numeric(8))
insert into test2 (id) values (1)
insert into test2 (id) values (4)
insert into test2 (id) values (3)
select * from test1
where id in (select cast(id as varchar(10)) from test2)
but i am getting no rows here......i want output as...records (id) present in test2 table
that should get from test1...
Regards,
Papillon
-
You have a de-normalized structure - storing multiple values in the same field violates the 1NF itself.
Since you are storing multiple values in TEST1.ID field and those are separated by commas, there isn't an exact match to TEST2.ID field. Right thing to do would be to design the tables correctly to have atomic values in the columns. If you are putting them together because those ID values share the same group attribute, design the tables such that you have a group_id field to be able to link them together.
With the data that you currently have, you would need to write a function to be able to split the values from TEST1.ID into a vertical column so that those values are present as singleton atomic values and then do your query or, do a row-by-row look up to see if the value in TEST2.ID exists in the TEST1.ID string of values.
-
Hi!!
I am getting such type of resords in Test1 table from the application...
So I cannot do any thing from there...so pls suggest me any solution to get the records from test1 table having Ids present in test2
Regards,
Papillon
-
Ok - here is the solution then - but be aware that there is no substitute to good design...in this scenario, your table design is really bad...as a result, we are hacking the SQL to get what you want:
/***************************************************************
--populate with running numbers
--so that it can be used to split
--the comma separated values from the column
--you can set the value higher than 1000
--if there are lots of records that are exepected from test1
--after the commas are removed and the list is prepared
***************************************************************/
CREATE TABLE #TEMP_ROWNUM(ROWNUM INT IDENTITY(1,1), COL2 INT)
INSERT INTO #TEMP_ROWNUM (COL2)
SELECT TOP 1000 A.ID
FROM SYSOBJECTS A, SYSOBJECTS B
/***************************************************************
--doing a cross join again
--bad thing, but can't help it
--given the table structures
***************************************************************/
SELECT DISTINCT B.*
FROM #TEMP_ROWNUM A, TEST1 B
WHERE RowNum <= len(',' + ID + ',') - 1
AND substring(',' + ID + ',', RowNum, 1) = ','
AND CAST(ltrim(rtrim(substring(',' + ID + ',', RowNum + 1,
charindex(',', ',' + ID + ',', RowNum + 1) - RowNum - 1))) AS INT)
IN (SELECT ID FROM TEST2)
--OUTPUT
ID NAME
1,7 ABCD
3,10 ABCD
6,4 ABCD
Look at :
http://www.sommarskog.se/arrays-in-sql.html#tblnum
to see the inline_split_me function - the above logic in the WHERE clause is similar to that function logic.
Hth
-
Hi!!
Thanks for ur help!!
But now it seems.. , that my database table design should be normal........I will check what else i can do to normalise my
database
Regards,
Papillon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply