Combining Data from 3 tables
-
I have three tables as follows:
Table 1 - holds PART, DESCR, SALES, PROD
e.g.
TEST, Test Part, SG1, PG2
Table 2 - holds list of size codes and description for that PART
e.g.
TEST, L, Large
TEST, M, Medium
Upto perhaps 10 sizes (but different amount of sizes for each part)
Table 3 - holds list of colours and descriptions
e.g.
TEST, RED, Red
TEST, BLU, Blue
Upto perhaps 20 colours (but different amount of colours for each part)
Need to create a number of output tables as follows:
All combinations of part e.g. in this case
TESTREDM
TESTREDL
TESTBLUM
TESTBLUL
Any ideas?
Thanks
-
Try this:
create table Table1 (
Part char(10) constraint pk_table1 primary key,
Descr varchar(20),
Sales char(3),
Prod char(3)
)
create table Table2 (
Part char(10),
SizeCD char(1),
Descr varchar(20),
constraint pk_table2 primary key (Part, SizeCD)
)
create table Table3 (
Part char(10),
ColorCD char(3),
Descr varchar(20),
constraint pk_table3 primary key (Part, ColorCD)
)
insert into Table1 values ('TEST', 'Test Part', 'SG1', 'PG2')
insert into Table2 values ('TEST', 'L', 'Large')
insert into Table2 values ('TEST', 'M', 'Medium')
insert into Table3 values ('TEST', 'RED', 'Red')
insert into Table3 values ('TEST', 'BLU', 'Blue')
select
t1.Part,
t3.ColorCD,
t2.SizeCD,
ltrim(rtrim(t1.Part)) + t3.ColorCD + t2.SizeCD as ConCat
from
Table1 t1
cross join Table2 t2
cross join Table3 t3
drop table Table1
drop table Table2
drop table Table3
hth,
Lynn
-
Lynn,
Thanks for the quick reply
That looks good. Now if I have multiple parts how can I build up a table that contains all the various combinations for all the parts and the colours/sizes associated with that those parts?
Thanks
Luke
-
Make this change and play with it some. I changed from a cross join to an inner join:
create table Table1 (
Part char(10) constraint pk_table1 primary key,
Descr varchar(20),
Sales char(3),
Prod char(3)
)
create table Table2 (
Part char(10),
SizeCD char(1),
Descr varchar(20),
constraint pk_table2 primary key (Part, SizeCD)
)
create table Table3 (
Part char(10),
ColorCD char(3),
Descr varchar(20),
constraint pk_table3 primary key (Part, ColorCD)
)
insert into Table1 values ('TEST', 'Test Part', 'SG1', 'PG2')
insert into Table2 values ('TEST', 'L', 'Large')
insert into Table2 values ('TEST', 'M', 'Medium')
insert into Table3 values ('TEST', 'RED', 'Red')
insert into Table3 values ('TEST', 'BLU', 'Blue')
insert into Table1 values ('TEST1', 'Test Part', 'SG1', 'PG2')
insert into Table2 values ('TEST1', 'L', 'Large')
insert into Table2 values ('TEST1', 'M', 'Medium')
insert into Table3 values ('TEST1', 'RED', 'Red')
insert into Table3 values ('TEST1', 'BLU', 'Blue')
select
t1.Part,
t3.ColorCD,
t2.SizeCD,
ltrim(rtrim(t1.Part)) + t3.ColorCD + t2.SizeCD as ConCat
from
Table1 t1
inner join Table2 t2
on (t1.Part = t2.Part)
inner join Table3 t3
on (t1.Part = t3.Part)
drop table Table1
drop table Table2
drop table Table3
hth,
Lynn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply