| 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 |
|
Extract a number from a string with like