CODEWITHSUNDEEP
sqlsql-servert-sql
wafw1971
wafw1971

Reputation: 361

How to create a third table based on two other tables

I have a table of Colour names

Red
Yellow
Green
Purple

I also have a Table of Coloured Tops

RedandGreen
Red
YellowandRedandGreen
Violet

What I want to do is create a third table where if I select Red it will show me all the tops with Red in and the other thing I want it to do is if I Select Purple it needs to bring back Violet.

Is this possible?

Thanks

W

Upvotes: 0

Views: 1000

Answers (5)

sgeddes
sgeddes

Reputation: 62841

Not sure why you'd want to create another table out of it, but would something like this work:

select c.color, t.colors
from color c 
  left join tops t on t.colors like '%' + replace(c.color,'Purple','Violet') + '%'

SQL Fiddle Demo

Then you can add your WHERE criteria as needed -- where c.color = 'Red' for example.

--EDIT

As others have suggested, consider using a lookup table for your colors. Something like this:

select c.color, t.colors
from color c 
  left join coloralias ca on c.color = ca.color
  left join tops t on  t.colors like '%' + c.color + '%' or t.colors like '%' + ca.alias + '%'

Another Demo

Upvotes: 0

Jeff B
Jeff B

Reputation: 545

I can't see any way to do this for the Purple = Violet than have another lookup table. For instance how is the system supposed to know that Light Blue and Cyan are the same color?

ColorShades
PrimaryColor|Shade
Purple|Purple
Purple|Violet
Blue|Blue
Blue|Cyan
...

Tops
TopColors
RedandGreen
Red
YellowandRedandGreen
Violet

SELECT TopColors
FROM Tops, ColorShades
WHERE ColorShades.PrimaryColor = Purple
AND Tops.TopColors like '%ColorShades.Shade%'

So you could have a table that translates various colors down to a couple of tables that you allow.

Assuming you're using this for a dropdown selection, you could make the source of that selection be SELECT DISTINCT PrimaryColor FROM ColorShades and have only one color definition table.

Upvotes: 2

Captain Kenpachi
Captain Kenpachi

Reputation: 7215

Yes, it's possible, but it's going to be horribly impractical. Especially when you start adding more and more shades of e.g. purple, like mauve, lavender, Lilac, etc. It's not a good idea.

What you will have to do is create a third table called colors_to_tops and when entering data, assign each top to every colour you want it returned for.

Colors table

id|color
--------
 1|Red
 2|Yellow
 3|Green
 4|Purple

Colored Tops

id|TopName
--------------
 1|RedandGreen
 2|Red
 3|YellowandRedandGreen
 4|Violet

colors_to_tops

id|colorID|topID
----------------
 1|      1|    1
 2|      1|    2
 3|      1|    3
 4|      2|    3
 5|      3|    1
 6|      3|    3
 7|      4|    4

SQL:

SELECT DISTINCT ct.* FROM
    ColoredTops AS ct
LEFT JOIN
    Colors_To_Tops AS ctt
ON
    ct.id = ctt.topID
LEFT JOIN
    Colors AS c
ON
    ctt.colorID = c.id

WHERE c.color = @color

Upvotes: 2

Ian Kenney
Ian Kenney

Reputation: 6426

You can create a join table:

create tables with ids 

CREATE TABLE colour ( 
 id int primary key,
 colour varchar(10)
);

create table tops (
  id int primary key,
  tops varchar(50)
  );

create a join table to relate them 

create table top_colours
(
  top_id int, 
  colour_id int
)

add your data 

insert into tops(id,tops)
select 
1, 'RedandGreen'
union select 
2, 'Red'
union select 
3, 'YellowandRedandGreen'
union select 
4, 'Violet';


insert into colour(id,colour)
select 1, 'Red'
union select 
2, 'Yellow'
union select 
3,'Green'
union select 
4,'Purple';

insert into top_colours(top_id,colour_id)
select 1,1 union
select 1,3 union
select 2,1 union
select 3,2 union
select 3,1 union
select 3,3 union
select 4,4;

ask away 

select distinct  tops.* from tops 
join top_colours tc on tc.top_id =  tops.id
join colour c on c.id = tc.colour_id 
where c.colour = 'red' ; 

select distinct  tops.* from tops 
join top_colours tc on tc.top_id =  tops.id
join colour c on c.id = tc.colour_id 
where c.colour = 'purple' ;

sql fiddle

Upvotes: 1

Vinicius Lima
Vinicius Lima

Reputation: 544

in my opinion, is not necessary create a third table to show your informations. If you link both tables using a correct foreign key, your job will work.

See:

create table color
(
id_color int not null,
ds_color varchar(50) null,
primary key (id_color)
)

create table color_tops
(
id_color int not null,
id_colortop int not null,
ds_colortop varchar(50) null,
primary key (id_colortop),
foreign key (id_color) references color(id_color)
)

select color.ds_color,
       color_tops.ds_colortops
from   color
       inner join color on (color.id_color = color_tops.ds_colortops)

Upvotes: 1

Related Questions