November 22, 2012 at 2:35 am
Hi, I'm working on normalizing a small part of our DB, but having trouble building a particular query that would enable to move data to an appropriate table.
Say, i have this table:
CREATE TABLE [#cars](
[id] [int] NOT NULL,
[part1] [int] NULL,
[part2] [int] NULL,
[part3] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [#cars] (id, [part1], [part2],[part3])
SELECT 1,1,1,1 UNION ALL
SELECT 2,0,0,0 UNION ALL
SELECT 3,0,1,0 UNION ALL
SELECT 4,1,0,0 UNION ALL
SELECT 5,0,0,1
SELECT * FROM #cars
DROP TABLE #cars
I want the select result to be:
carid | carpart
1 | 1
1 | 2
1 | 3
3 | 2
4 | 2
5 | 3
Thanks for your time.
November 22, 2012 at 2:44 am
ah, solved it with lots of unions... sometimes it helps to write it in text...
ill post the query in a sec, if someone else needs it.
UPDATE: here's the query.
SELECT id as carid, '1' as carparts FROM #cars WHERE part1=1
UNION
SELECT id as carid, '2' as carparts FROM #cars WHERE part2=1
UNION
SELECT id as carid, '3' as carparts FROM #cars WHERE part3=1
November 22, 2012 at 4:27 am
This was removed by the editor as SPAM
November 22, 2012 at 4:51 am
what about this:
select id, a.PartId
from #cars
cross apply (VALUES (1*[part1]), (2*[part2]), (3*[part3])) a(PartId)
where a.PartId > 0
November 22, 2012 at 10:24 pm
+1.
select id,1*part1 from #cars where part1 > 0
union
select id,2*part2 from #cars where part2 > 0
union
select id,3*part3 from #cars where part3 > 0
karthik
November 26, 2012 at 6:18 am
I don't think your table is normalized. Your have two entities, cars and parts. One car can have several parts, on part can be used in several cars. That's an m:n-relationship. This has to be realized by two 1:n-relationships.
CREATE TABLE #cars(
[id] [int] NOT NULL primary key( id )
) ON [PRIMARY]
create table #parts(
Id int not null
primary key (id )
)
create table #mnPartsCars(
CarId int not null,
PartId int not null
);
insert into #cars
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
insert into #parts( Id ) values ( 1 ), ( 2 ), ( 3 );
insert into #mnPartsCars( CarId, PartId ) values
( 1, 1 ),
( 1, 2 ),
( 1, 3 ),
( 3, 2),
( 4, 1),
( 5, 3);
select
CarId = c.id,
PartId = p.id
from
#cars c inner join
#mnPartsCars mn on
mn.CarId = c.id inner join
#parts p on
p.Id = mn.PartId
drop table #cars
drop table #parts
drop table #mnPartsCars
There would be two foreign keys on the table #mnPartsCars, one referencing the #Cars-table, one referencing the #Parts-table.
November 26, 2012 at 6:29 am
I don't think your table is normalized. Your have two entities, cars and parts. One car can have several parts, on part can be used in several cars. That's an m:n-relationship. This has to be realized by two 1:n-relationships.
You're right it wasn't normalized, that why i needed the query to do an insert into the new normalized table.
My new structure looks like the one you suggested.
November 26, 2012 at 6:53 am
ahaaaaaaa 🙂 now I understood
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply