Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5SEXIa54986 for ; Thu, 28 Jun 2001 10:33:18 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 2.12 #1) id 15FcrV-000JHD-0W; Thu, 28 Jun 2001 15:33:41 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id AA09812A67; Thu, 28 Jun 2001 15:31:49 +0100 (BST) Message-ID: <001201c0ffdf$0fbfecc0$1001a8c0@archonet.com> From: "Richard Huxton" To: "Christian Anton" Cc: References: <3b3ab3aa_1@newsa.ev1.net> Subject: Re: Link many attributes in one table to one attribute in another?? Date: Thu, 28 Jun 2001 15:31:44 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/447 X-Sequence-Number: 3750 "Christian Anton" 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