Thread: optional reference
Hello, What is a technique to reference some (but not all) rows from table a to a row in table b? For example: --------------------------- DROP TABLE a; DROP TABLE b; CREATE TABLE a ( column_1 character(8), column_2 character(2), column_3 character(40) ); INSERT INTO a VALUES ('20901234', '01', 'This is a the first row'), ('20901234', '01', 'This is the second row'), ('20901234', '01', 'This is the third row'), ('20901235', '01', 'This is the fourth row'), ('20901236', '01', 'This is the fifth row'), ('20901236', '01', 'This is the sixth row'), ('20901237', '01', 'This is the seventh row'), ('20901238', '01', 'This is the eighth row'); CREATE TABLE b ( column_1 character(8), column_2 character(2), column_3 character(40), PRIMARY KEY (column_1, column_2) ); INSERT INTO b VALUES ('20901234', '01', 'Footnote #1'), ('20901234', '02', 'other stuff'), ('20901237', '01', 'Footnote #2'); -------------------------------------- Table a rows 1, 2 & 3 should point to table b row 1. Table a row 7 should point to table b row 3. Any guidance would be appreciated. Thanks, Bill House
create a third table to store your relations only.
Both the tables will be independent (a and b) i mean they will not have any direct relations.
--
Pushpendra Singh Thakur
COREEXPERTS Technologies Private Limited
Business Process Automation & IT Support Services
http://www.corexprts.com
Phone - 91-761-4070036
Fax - 91-761-4010530
SMS - 91-799-66554
Relation table C
create table C(
a_col1 char(8) references a(column_1),
b_col1 char(8) references b(column_1))
Both the tables will be independent (a and b) i mean they will not have any direct relations.
2010/1/24 bill house <wchouse@bellsouth.net>
Hello,
What is a technique to reference some (but not all) rows from table a to a row in table b?
For example:
---------------------------
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a (
column_1 character(8),
column_2 character(2),
column_3 character(40)
);
INSERT INTO a VALUES
('20901234', '01', 'This is a the first row'),
('20901234', '01', 'This is the second row'),
('20901234', '01', 'This is the third row'),
('20901235', '01', 'This is the fourth row'),
('20901236', '01', 'This is the fifth row'),
('20901236', '01', 'This is the sixth row'),
('20901237', '01', 'This is the seventh row'),
('20901238', '01', 'This is the eighth row');
CREATE TABLE b (
column_1 character(8),
column_2 character(2),
column_3 character(40),
PRIMARY KEY (column_1, column_2)
);
INSERT INTO b VALUES
('20901234', '01', 'Footnote #1'),
('20901234', '02', 'other stuff'),
('20901237', '01', 'Footnote #2');
--------------------------------------
Table a rows 1, 2 & 3 should point to table b row 1.
Table a row 7 should point to table b row 3.
Any guidance would be appreciated.
Thanks,
Bill House
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
Pushpendra Singh Thakur
COREEXPERTS Technologies Private Limited
Business Process Automation & IT Support Services
http://www.corexprts.com
Phone - 91-761-4070036
Fax - 91-761-4010530
SMS - 91-799-66554
Hi. On Sunday 24 Jan 2010 at 10:00 Pushpendra Singh Thakur wrote: > create a third table to store your relations only. > Relation table C > create table C( > a_col1 char(8) references a(column_1), > b_col1 char(8) references b(column_1)) > > Both the tables will be independent (a and b) i mean they will not have any > direct relations. This is certainly the approach I'd use (it lets you link any row in a to any combination of rows in b), but your suggestion won't work for the example in the original question, since a(column_1) contains non-unique values. The relations table should only reference primary keys in the other tables. I'd add a serial primary key to both tables and use that in the third table, but the poster may have a need for the compound text key on b that prevents this. Anyway, assuming PK changes: CREATE TABLE a ( row_id SERIAL PRIMARY KEY, column_1 character(8), column_2 character(2), column_3 character(40) ); CREATE TABLE b ( row_id SERIAL PRIMARY KEY, column_1 character(8), column_2 character(2), column_3 character(40) ); -- Added unique constraint to stop you accidentally adding the same link -- twice, and some cascades to maintain the link table integrity. CREATE TABLE c ( link_a INTEGER REFERENCES a(row_id) ON UPDATE CASCADE ON DELETE CASCADE, link_b INTEGER REFERENCES b(row_id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE (link_a,link_b) ); INSERT INTO a (column_1,column_2,column_3) VALUES ('20901234', '01', 'This is a the first row'), ('20901234', '01', 'This is the second row'), ('20901234', '01', 'This is the third row'), ('20901235', '01', 'This is the fourth row'), ('20901236', '01', 'This is the fifth row'), ('20901236', '01', 'This is the sixth row'), ('20901237', '01', 'This is the seventh row'), ('20901238', '01', 'This is the eighth row'); INSERT INTO b (column_1,column_2,column_3) VALUES ('20901234', '01', 'Footnote #1'), ('20901234', '02', 'other stuff'), ('20901237', '01', 'Footnote'); -- This assumes the PKs started counting from 1 (default) INSERT INTO c VALUES ('1','1'), ('2','1'), ('3','1'), ('7','3'); -- get all from b linked to row 1 in a SELECT b.* FROM b,c WHERE b.row_id = c.link_b AND c.link_a = '1'; Cheers, Mark
You are absolutely correct. My idea only works with unique fields.
--
Pushpendra Singh Thakur
COREEXPERTS Technologies Private Limited
Business Process Automation & IT Support Services
http://www.corexprts.com
Phone - 91-761-4070036
Fax - 91-761-4010530
SMS - 91-799-66554
2010/1/24 Mark Kelly <pgsql@wastedtimes.net>
Hi.This is certainly the approach I'd use (it lets you link any row in a to any
On Sunday 24 Jan 2010 at 10:00 Pushpendra Singh Thakur wrote:
> create a third table to store your relations only.
> Relation table C
> create table C(
> a_col1 char(8) references a(column_1),
> b_col1 char(8) references b(column_1))
>
> Both the tables will be independent (a and b) i mean they will not have any
> direct relations.
combination of rows in b), but your suggestion won't work for the example in
the original question, since a(column_1) contains non-unique values. The
relations table should only reference primary keys in the other tables.
I'd add a serial primary key to both tables and use that in the third table,
but the poster may have a need for the compound text key on b that prevents
this.
Anyway, assuming PK changes:
CREATE TABLE a (
row_id SERIAL PRIMARY KEY,column_1 character(8),CREATE TABLE b (
column_2 character(2),
column_3 character(40)
);
row_id SERIAL PRIMARY KEY,column_1 character(8),-- Added unique constraint to stop you accidentally adding the same link
column_2 character(2),
column_3 character(40)
);
-- twice, and some cascades to maintain the link table integrity.
CREATE TABLE c (
link_a INTEGER REFERENCES a(row_id) ON UPDATE CASCADE ON DELETE CASCADE,
link_b INTEGER REFERENCES b(row_id) ON UPDATE CASCADE ON DELETE CASCADE,
UNIQUE (link_a,link_b)
);
INSERT INTO a (column_1,column_2,column_3) VALUES('20901234', '01', 'This is a the first row'),INSERT INTO b (column_1,column_2,column_3) VALUES
('20901234', '01', 'This is the second row'),
('20901234', '01', 'This is the third row'),
('20901235', '01', 'This is the fourth row'),
('20901236', '01', 'This is the fifth row'),
('20901236', '01', 'This is the sixth row'),
('20901237', '01', 'This is the seventh row'),
('20901238', '01', 'This is the eighth row');('20901234', '01', 'Footnote #1'),('20901237', '01', 'Footnote');
('20901234', '02', 'other stuff'),
-- This assumes the PKs started counting from 1 (default)
INSERT INTO c VALUES
('1','1'),
('2','1'),
('3','1'),
('7','3');
-- get all from b linked to row 1 in a
SELECT b.* FROM b,c WHERE b.row_id = c.link_b AND c.link_a = '1';
Cheers,
Mark
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
Pushpendra Singh Thakur
COREEXPERTS Technologies Private Limited
Business Process Automation & IT Support Services
http://www.corexprts.com
Phone - 91-761-4070036
Fax - 91-761-4010530
SMS - 91-799-66554
Thanks for the help guys, Since I really am a novice, I will have to digest this. If I learn anything to share on this subject, I will post again. Thanks again Bill House Pushpendra Singh Thakur wrote: > You are absolutely correct. My idea only works with unique fields. > > 2010/1/24 Mark Kelly <pgsql@wastedtimes.net> > >> Hi. >> >> On Sunday 24 Jan 2010 at 10:00 Pushpendra Singh Thakur wrote: >> >>> create a third table to store your relations only. >>> Relation table C >>> create table C( >>> a_col1 char(8) references a(column_1), >>> b_col1 char(8) references b(column_1)) >>> >>> Both the tables will be independent (a and b) i mean they will not have >> any >>> direct relations. >> This is certainly the approach I'd use (it lets you link any row in a to >> any >> combination of rows in b), but your suggestion won't work for the example >> in >> the original question, since a(column_1) contains non-unique values. The >> relations table should only reference primary keys in the other tables. >> >> I'd add a serial primary key to both tables and use that in the third >> table, >> but the poster may have a need for the compound text key on b that prevents >> this. >> >> Anyway, assuming PK changes: >> >> CREATE TABLE a ( >> row_id SERIAL PRIMARY KEY, >> column_1 character(8), >> column_2 character(2), >> column_3 character(40) >> ); >> >> CREATE TABLE b ( >> row_id SERIAL PRIMARY KEY, >> column_1 character(8), >> column_2 character(2), >> column_3 character(40) >> ); >> >> -- Added unique constraint to stop you accidentally adding the same link >> -- twice, and some cascades to maintain the link table integrity. >> CREATE TABLE c ( >> link_a INTEGER REFERENCES a(row_id) ON UPDATE CASCADE ON DELETE CASCADE, >> link_b INTEGER REFERENCES b(row_id) ON UPDATE CASCADE ON DELETE CASCADE, >> UNIQUE (link_a,link_b) >> ); >> >> INSERT INTO a (column_1,column_2,column_3) VALUES >> ('20901234', '01', 'This is a the first row'), >> ('20901234', '01', 'This is the second row'), >> ('20901234', '01', 'This is the third row'), >> ('20901235', '01', 'This is the fourth row'), >> ('20901236', '01', 'This is the fifth row'), >> ('20901236', '01', 'This is the sixth row'), >> ('20901237', '01', 'This is the seventh row'), >> ('20901238', '01', 'This is the eighth row'); >> >> INSERT INTO b (column_1,column_2,column_3) VALUES >> ('20901234', '01', 'Footnote #1'), >> ('20901234', '02', 'other stuff'), >> ('20901237', '01', 'Footnote'); >> >> -- This assumes the PKs started counting from 1 (default) >> INSERT INTO c VALUES >> ('1','1'), >> ('2','1'), >> ('3','1'), >> ('7','3'); >> >> -- get all from b linked to row 1 in a >> SELECT b.* FROM b,c WHERE b.row_id = c.link_b AND c.link_a = '1'; >> >> Cheers, >> >> Mark >> >> -- >> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-novice >> > > >