Normalization query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Normalization query help

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2012-09-13 : 12:26:31

Hello Friends,

I am trying to analyze and optimize DB structure and I think normalization is a way to go. Here's my table structure:

Table : company

cid cname
--------------------------------------------------------------------
1 apple
2 google
3 microsoft
4 samsung
5 motorola

Table: product

pid pname appleid googleid microsoftid samsungid motorolaid
----------------------------------------------------------------------
1 iphone 5 100 null null null null
2 galaxy s3 null null null 101 null
3 lumia 920 null null 102 null null
4 google nexus null 103 null null null
5 droid razr m null null null null 104

So I came up with a following new lookup table .

Table: companyproduct

pid cid companyproductid
----------------------------------------------------------------------
1 1 100
2 4 101
3 3 102
4 2 103
5 5 104

My issue is to write a query which insers above data in companyproduct table.

insert into companyproduct
select p.pid,c.cid, ?
from product p ,company c ....

Please give solution in sql 2000 since this DB is actually in mysql.

Many thanks in advance.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-13 : 12:45:14

It's probably easiest just to write five insert statements. For example:

insert 
    companyproduct
select 
    pid,1, AppleID
from 
    product 
where
    AppleID IS NOT NULL


insert 
    companyproduct
select 
    pid,2, GoogleID
from 
    product 
where
    GoogleID IS NOT NULL

...

Otherwise I think you'd have to do some sort of piviot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:11:56

Please give solution in sql 2000 since this DB is actually in mysql.

still whats guarantee that it will work in mysql?

you may better off posting this in mysql forum IMHO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ssunny
Posting Yak Master

133 Posts

Posted - 2012-09-13 : 13:39:35

Lamprey - Thank you for your help. I will use the same approach.

Visakh - You are probably correct but I have converted lot of sql 2000 queries into mysql and thought if I can get the simple "insert into select from " query, it will work in mysql without too much modification. And this is my home ground. I know I will get a quick reply for any question on this forum.

Thnaks again guys!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:50:15

i have solution which uses pivot but not sure mysql supports it
ie like


INSERT companyproduct
SELECT pid,cid,Val
FROM
(
SELECT pid,REPLACE(Cat,'id','') AS Cat,Val
FROM product
UNPIVOT (Val FOR Cat IN ([appleid], [googleid], [microsoftid], [samsungid] [motorolaid]))u
)t
INNER JOIN company c
ON c.cname = t.Cat
WHERE t.Val IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ssunny
Posting Yak Master

133 Posts

Posted - 2012-09-13 : 14:04:58

Visakh,I tried your UNPIVOT query but I and getting a syntax error and I think it's not supported in mysql. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 14:25:29

quote:
Originally posted by ssunny

Visakh,I tried your UNPIVOT query but I and getting a syntax error and I think it's not supported in mysql. Thank you.

Hope now you got my earlier point

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ssunny
Posting Yak Master

133 Posts

Posted - 2012-09-13 : 14:39:29

Haha got it bro. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 14:41:09

quote:
Originally posted by ssunny

Haha got it bro. Thanks.

try your luck at mysql forum by posting query and asking for help on convertion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ssunny
Posting Yak Master

133 Posts

Posted - 2012-09-13 : 15:08:49

[/quote]
try your luck at mysql forum by posting query and asking for help on convertion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]

Actually I got it working using Lamprey's solution since this is only a one time deal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources