How to handle One-to-Many Join without repeat rows

[alanv72]

I am trying to join 3 tables.

Two of them (Name and PRMF) have a one-to-one relationship where the fields Name_ID=Owner_ID. I have a 3rd table (PLGL) that I need to include for just one field called "Legal", but the table PRMF (primary key is RPID) has a one-to-many relationship with the fields RPID=RPID. The PLGL table has a primary key of "RPID" and a foreign key of "Sequence" which is auto incrementing. When I join on the 3rd table with "WHERE PRMF.RPID=PLGL.RPID", I get one row for every "Legal" for that same RPID. And some RPIDs have different numbers of "Legal" vaules assigned to them.

I'm wanting to display one row with all of the "Legal" descriptions for the each RPID in one field. I would like to keep this on the SQL side if possible. Is there someway that I can do this and concatenate all of the "Legal" values together?

Any help would be appreciated. If I need to clarify anything, please let me know.

Thanks,
alanv72

[aspdaddy]

>foreign key of "Sequence" which is auto incrementing

I think the problem is there, when a new record is created, its foreign key should match the primary key in the parent table, related record, not auto-increment.

[coopster]

Is there someway that I can do this and concatenate all of the "Legal" values together?

Yes, you can, but it will depend on the database you are using. I'll use MySQL to demonstrate the GROUP BY Function [mysql.com],

GROUP_CONCAT

(MySQL >= 4.1).

Disclaimer:

MySQL Server includes some extensions that you probably will not find in other SQL databases. Be warned that if you use them, your code will not be portable to other SQL servers.
Source: MySQL Extensions to the SQL-92 Standard [mysql.com]

CREATE TABLE person ( 
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  name CHAR(60) NOT NULL, 
  PRIMARY KEY (id) 
); 
CREATE TABLE shirt ( 
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  style ENUM('t-shirt', 'polo', 'dress') NOT NULL, 
  color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, 
  owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id), 
  PRIMARY KEY (id) 
); 

INSERT INTO person VALUES (NULL, 'Antonio Paz'); 

INSERT INTO shirt VALUES 
(NULL, 'polo', 'blue', LAST_INSERT_ID()), 
(NULL, 'dress', 'white', LAST_INSERT_ID()), 
(NULL, 't-shirt', 'blue', LAST_INSERT_ID()); 

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska'); 

INSERT INTO shirt VALUES 
(NULL, 'dress', 'orange', LAST_INSERT_ID()), 
(NULL, 'polo', 'red', LAST_INSERT_ID()), 
(NULL, 'dress', 'blue', LAST_INSERT_ID()), 
(NULL, 't-shirt', 'white', LAST_INSERT_ID()); 

SELECT * FROM person; 
+----+---------------------+ 
¦ id ¦ name                ¦ 
+----+---------------------+ 
¦  1 ¦ Antonio Paz         ¦ 
¦  2 ¦ Lilliana Angelovska ¦ 
+----+---------------------+ 

SELECT * FROM shirt; 
+----+---------+--------+-------+ 
¦ id ¦ style   ¦ color  ¦ owner ¦ 
+----+---------+--------+-------+ 
¦  1 ¦ polo    ¦ blue   ¦   1   ¦ 
¦  2 ¦ dress   ¦ white  ¦   1   ¦ 
¦  3 ¦ t-shirt ¦ blue   ¦   1   ¦ 
¦  4 ¦ dress   ¦ orange ¦   2   ¦ 
¦  5 ¦ polo    ¦ red    ¦   2   ¦ 
¦  6 ¦ dress   ¦ blue   ¦   2   ¦ 
¦  7 ¦ t-shirt ¦ white  ¦   2   ¦ 
+----+---------+--------+-------+ 

SELECT name, 
 GROUP_CONCAT(style ORDER BY style SEPARATOR " ") as styles 
 FROM person 
 LEFT JOIN shirt ON (person.id=shirt.owner) 
 GROUP BY shirt.owner; 

+---------------------+--------------------------+ 
¦ name                ¦ styles                   ¦ 
+---------------------+--------------------------+ 
¦ Antonio Paz         ¦ polo t-shirt dress       ¦ 
¦ Lilliana Angelovska ¦ polo dress t-shirt dress ¦ 
+---------------------+--------------------------+