Author |
Topic  |
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 02/28/2013 : 04:20:34
|
Hello everybody, The title may not be very clear, but here is my problem:
Table mat id_mat(int) | name_en(nvarchar) 1 | Steel 2 | Aluminium 3 | Leather ......
Table product id_product(int) | type_mat(nvarchar) 1000 | 1, 2 1001 | 2 1002 | 1, 3, 10 ......
I would to SELECT every id_mat FROM mat who are IN type_mat FROM product. Of course id_mat 1 won't comes out when it is a 10 or 11.... I tried with an other column type_mat2 as well where the string looks like $1$$3$10$ to do a like '%$...$%'. Of course when I tried to concat piece of string with my id_mat it didn't work ('S'+id_mat+'S'). Ok I can see your smiling now ;-) Any help will appreciated. Thanks
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 02/28/2013 : 04:38:35
|
The real solution is to have a third table to map products with materials...
type_mat in that form 1,3,10 is a mess!
Too old to Rock'n'Roll too young to die. |
 |
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 02/28/2013 : 04:52:06
|
Here is a messy solution for a messy database:
-- declare test tables and insert testdata
declare @mat Table(id_mat int, name_en nvarchar(30))
insert @mat
select 1, 'Steel' union all
select 2, 'Aluminium' union all
select 3, 'Leather'
declare @product Table(id_product int, type_mat nvarchar(30))
insert @product
select 1000, '1, 2' union all
select 1001, '2' union all
select 1002, '1, 3, 10'
-- the solution
select p.*, m.name_en
from @product p
left join @mat m on ','+replace(p.type_mat,' ','')+',' like '%,'+convert(nvarchar(30),m.id_mat)+',%'
Too old to Rock'n'Roll too young to die. |
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 02/28/2013 : 06:16:23
|
Thanks webfred, I agree with the messy comment! I didn't build this database but inherited it (and not from my grand mother)... So I have to deal with it, and I am thinking about your solution with a 3rd table. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 02/28/2013 : 06:24:07
|
another way is this
-- declare test tables and insert testdata
declare @mat Table(id_mat int, name_en nvarchar(30))
insert @mat
select 1, 'Steel' union all
select 2, 'Aluminium' union all
select 3, 'Leather'
declare @product Table(id_product int, type_mat nvarchar(30))
insert @product
select 1000, '1, 2' union all
select 1001, '2' union all
select 1002, '1,3,10'
select p.id_product,m.name_en
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval
from @product) p
WHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1
ORDER BY p.id_product
output
------------------------------------
id_product name_en
------------------------------------
1000 Steel
1000 Aluminium
1001 Aluminium
1002 Leather
1002 Steel
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 02/28/2013 : 06:24:32
|
What do you suggest to extract properly the type_mat in a new table? in a way to have: id_product | id_mat 1000 | 1 1000 | 2 1001 | 2 ..... |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 02/28/2013 : 06:34:54
|
Thank you so much visakh16. It works perfectly! |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 02/28/2013 : 10:00:12
|
welcome
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 02/28/2013 : 11:30:58
|
Now I need to select this results into my new table. But keep having error messages. Could you give the key please? |
 |
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 02/28/2013 : 11:39:22
|
If the table already exists, and assuming the columns in that table are id_product and name_en do this:-- declare test tables and insert testdata
declare @mat Table(id_mat int, name_en nvarchar(30))
insert @mat
select 1, 'Steel' union all
select 2, 'Aluminium' union all
select 3, 'Leather'
declare @product Table(id_product int, type_mat nvarchar(30))
insert @product
select 1000, '1, 2' union all
select 1001, '2' union all
select 1002, '1,3,10'
INSERT INTO YourNewTable (id_product, name_en)
select p.id_product,m.name_en
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval
from @product) p
WHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1
ORDER BY p.id_product
|
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 02/28/2013 : 11:44:32
|
Sorry, it worked! It was just a conflict with primary keys! |
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 02/28/2013 : 11:46:19
|
Thank you James K. |
 |
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 02/28/2013 : 12:01:47
|
You are very welcome - glad to help. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 02/28/2013 : 12:47:12
|
quote: Originally posted by Lesombrero
Sorry, it worked! It was just a conflict with primary keys!
In case conflicts occur use NOT EXISTS based check before you do actual insert
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 03/01/2013 : 01:51:01
|
Thanks for the tip visakh16 |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 03/01/2013 : 02:01:39
|
welcome
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 03/01/2013 : 03:44:37
|
Now an other/almost same one ;-) How can use your solution to output only the products with 2 in type_mat I guess it would be something like (ok now I am turning red): select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval from @product p WHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1 ORDER BY p.id_product
Thanks in advance
|
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 03/01/2013 : 03:50:49
|
it should be
select distinct p.id_product
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval
from @product) p
WHERE xmlval.exist('/Root/Node[.="2"]')=1
ORDER BY p.id_product
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
Lesombrero
Starting Member
Germany
43 Posts |
Posted - 03/01/2013 : 04:09:51
|
But with this I get one output (1001) but 2 is also in id_product 1000 I actually don't want to go through the @mat table on this one. Just the id_products with a 2 in type_mat Thanks |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 03/01/2013 : 04:38:37
|
quote: Originally posted by Lesombrero
But with this I get one output (1001) but 2 is also in id_product 1000 I actually don't want to go through the @mat table on this one. Just the id_products with a 2 in type_mat Thanks
thats because you've a space before 2 for id 1000
do this small change and it will work fine
select distinct p.id_product
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(replace(type_mat,' ',''),',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval
from @product) p
WHERE xmlval.exist('/Root/Node[.="2"]')=1
ORDER BY p.id_product
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
Topic  |
|