pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Christian Anton <christiananton@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Link many attributes in one table to one attribute in another??
Date: Thu, 28 Jun 2001 15:31:44 +0100
Message-ID: <001201c0ffdf$0fbfecc0$1001a8c0@archonet.com> (raw)
References: <3b3ab3aa_1@newsa.ev1.net>

"Christian Anton" <christiananton@hotmail.com> wrote in message
news:3b3ab3aa_1@newsa.ev1.net...

> 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




view thread (4+ messages)  latest in thread

Message-ID: <001201c0ffdf$0fbfecc0$1001a8c0@archonet.com>
Permalink:  ../001201c0ffdf$0fbfecc0$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/001201c0ffdf$0fbfecc0$1001a8c0@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dev@archonet.com, christiananton@hotmail.com
  Subject: Re: Link many attributes in one table to one attribute in another??
  In-Reply-To: <001201c0ffdf$0fbfecc0$1001a8c0@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox