Author |
Topic |
Lesombrero
Starting Member
43 Posts |
Posted - 2013-02-28 : 04:20:34
|
Hello everybody,The title may not be very clear, but here is my problem:Table matid_mat(int) | name_en(nvarchar)1 | Steel2 | Aluminium3 | Leather......Table productid_product(int) | type_mat(nvarchar)1000 | 1, 21001 | 21002 | 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-28 : 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-28 : 04:52:06
|
Here is a messy solution for a messy database:-- declare test tables and insert testdatadeclare @mat Table(id_mat int, name_en nvarchar(30))insert @matselect 1, 'Steel' union allselect 2, 'Aluminium' union allselect 3, 'Leather'declare @product Table(id_product int, type_mat nvarchar(30))insert @productselect 1000, '1, 2' union allselect 1001, '2' union allselect 1002, '1, 3, 10'-- the solutionselect p.*, m.name_enfrom @product pleft 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
43 Posts |
Posted - 2013-02-28 : 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
52326 Posts |
Posted - 2013-02-28 : 06:24:07
|
another way is this-- declare test tables and insert testdatadeclare @mat Table(id_mat int, name_en nvarchar(30))insert @matselect 1, 'Steel' union allselect 2, 'Aluminium' union allselect 3, 'Leather'declare @product Table(id_product int, type_mat nvarchar(30))insert @productselect 1000, '1, 2' union allselect 1001, '2' union allselect 1002, '1,3,10'select p.id_product,m.name_enfrom @mat mcross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval from @product) pWHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1ORDER BY p.id_productoutput------------------------------------id_product name_en------------------------------------1000 Steel1000 Aluminium1001 Aluminium1002 Leather1002 Steel ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-02-28 : 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_mat1000 | 11000 | 21001 | 2..... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-02-28 : 06:34:54
|
Thank you so much visakh16.It works perfectly! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 10:00:12
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-02-28 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 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 testdatadeclare @mat Table(id_mat int, name_en nvarchar(30))insert @matselect 1, 'Steel' union allselect 2, 'Aluminium' union allselect 3, 'Leather'declare @product Table(id_product int, type_mat nvarchar(30))insert @productselect 1000, '1, 2' union allselect 1001, '2' union allselect 1002, '1,3,10'INSERT INTO YourNewTable (id_product, name_en)select p.id_product,m.name_enfrom @mat mcross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval from @product) pWHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1ORDER BY p.id_product |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-02-28 : 11:44:32
|
Sorry, it worked! It was just a conflict with primary keys! |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-02-28 : 11:46:19
|
Thank you James K. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 12:01:47
|
You are very welcome - glad to help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-03-01 : 01:51:01
|
Thanks for the tip visakh16 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 02:01:39
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-03-01 : 03:44:37
|
Now an other/almost same one ;-)How can use your solution to output only the products with 2 in type_matI 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 pWHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1ORDER BY p.id_productThanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 03:50:49
|
it should beselect distinct p.id_productfrom @mat mcross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval from @product) pWHERE xmlval.exist('/Root/Node[.="2"]')=1ORDER BY p.id_product ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-03-01 : 04:09:51
|
But with this I get one output (1001) but 2 is also in id_product 1000I actually don't want to go through the @mat table on this one.Just the id_products with a 2 in type_matThanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 04:38:37
|
quote: Originally posted by Lesombrero But with this I get one output (1001) but 2 is also in id_product 1000I actually don't want to go through the @mat table on this one.Just the id_products with a 2 in type_matThanks
thats because you've a space before 2 for id 1000do this small change and it will work fineselect distinct p.id_productfrom @mat mcross join (select id_product,cast('<Root><Node>'+ replace(replace(type_mat,' ',''),',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval from @product) pWHERE xmlval.exist('/Root/Node[.="2"]')=1ORDER BY p.id_product ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Next Page
|