From: | "Richard Huxton" <dev(at)archonet(dot)com> |
---|---|
To: | "Christian Anton" <christiananton(at)hotmail(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Link many attributes in one table to one attribute in another?? |
Date: | 2001-06-28 14:31:44 |
Message-ID: | 001201c0ffdf$0fbfecc0$1001a8c0@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"Christian Anton" <christiananton(at)hotmail(dot)com> wrote in message
news:3b3ab3aa_1(at)newsa(dot)ev1(dot)net(dot)(dot)(dot)
> I have a table with a list of marbles, each marble has three colors in it
> (color1, color2, color3) and a serial number. In another table I have
eight
> colors to choose from but the list of colors grows regularly.
> How do I associate a marble with three colors from the color table (a
marble
> may have three red sides or red-yellow-blue)?
Not sure what you mean exactly, but the following:
CREATE TABLE marbles (
m_id serial,
m_col1 int4,
m_col2 int4,
m_col3 int4
);
CREATE TABLE colours (
c_id serial,
c_name text
);
INSERT INTO colours (c_name) VALUES ('red');
INSERT INTO colours (c_name) VALUES ('green');
INSERT INTO colours (c_name) VALUES ('blue');
INSERT INTO marbles (m_col1,m_col2,m_col3) VALUES (1,1,1);
INSERT INTO marbles (m_col1,m_col2,m_col3) VALUES (1,2,3);
INSERT INTO marbles (m_col1,m_col2,m_col3) VALUES (3,2,1);
SELECT m.m_id, c1.c_name as colname1, c2.c_name as colname2, c3.c_name as
colname3
FROM marbles m
JOIN colours c1 ON m.m_col1 = c1.c_id
JOIN colours c2 ON m.m_col2 = c2.c_id
JOIN colours c3 ON m.m_col3 = c3.c_id;
Will produce:
m_id | colname1 | colname2 | colname3
------+----------+----------+----------
1 | red | red | red
3 | blue | green | red
2 | red | green | blue
We join the colours table to the marbles table three times and need to alias
it differently each time. Otherwise, we don't know which colour-name matches
which colour-code.
If it's not that simple, can we have table definitions and an example of the
sort of output you'd like?
- Richard Huxton
From | Date | Subject | |
---|---|---|---|
Next Message | Ilan Fait | 2001-06-28 14:34:03 | examine the database |
Previous Message | Ilan Fait | 2001-06-28 14:14:07 |