Schoolboy database creation error?

dtw · 2006-08-18 11:34:31

I think I may have made a huge fuck up in the creation of a database for work (shhhhh don't tell anyone.)

I have a table I, that has two fields, a and b. Both a and b are ID fields linked to table II.

So, basically I wanted a in table I to link to a record in table II and b in table I to link to a different record in table II.

I now think that is impossible though..help :'(

rhfrommn · 2006-08-18 14:59:12

I'm not sure I understand your post because it seems there isn't a problem. Here is an example showing what I'm thinking you have:

Table I
col a = first initial col b = last initial

Table II
col 1 = first name col 2 = last name col 3 = address col 4 = phone #

So you link col a to col 1 and col b to col 2

If Table II is
Joe Smith 123 Maple Drive 123-4567
Mary Wilson 543 Oak Street 678-5432

Then Table I is

J S
M W

Ok, assuming my example is right I don't see what the problem is. If it is wrong, what am I misunderstanding?

dtw · 2006-08-18 15:07:27

I'm not sure there is a problem now I was just having trouble constructing a query to read the data from the table. Just needed a quantum step up in complexity that was all! I think it is ok now. Thanks though!

phrakture · 2006-08-18 15:15:38

Sooo.... you want this (GO MSPAINT GO!!):

soloport · 2006-08-18 15:30:12

Like this?

create table location {
    id serial,
    name text NOT NULL
};

create table agent {
    id serial,
    name text NOT NULL,
    aka text NOT NULL,
    passphrase text NOT NULL
};

create table pinpoint {     -- table I
    id serial,
    agent integer NOT NULL,
    location text NOT NULL,
    datetime timestamp NOT NULL
};

create table trip {         -- table II
    id serial,
    departure integer NOT NULL, -- field a
    arrival integer NOT NULL    -- field b
};

insert into location (name) values ('Hong Kong');   -- 1 (id serial)
insert into location (name) values ('Tokio');       -- 2
insert into location (name) values ('London');      -- 3
insert into location (name) values ('Los Angeles'); -- 4
insert into location (name) values ('New York');    -- 5

insert into agent (name, aka, passphrase) values ('John Smith', 'James Bond', 'passphrase');  -- 1
insert into agent (name, aka, passphrase) values ('Dave Jones', 'Derek Flint', 'passphraze'); -- 2
insert into agent (name, aka, passphrase) values ('Richie Cunningham', 'Austin D. Powers', 'stupid pass frase');

insert into pinpoint (agent, location, datetime) values (1, 1, '2006-12-5 23:20:00 GMT'); -- 1
insert into pinpoint (agent, location, datetime) values (2, 5, '2006-12-3 09:23:00 GMT'); -- 2
insert into pinpoint (agent, location, datetime) values (3, 2, '2006-12-6 10:08:00 GMT'); -- 3
insert into pinpoint (agent, location, datetime) values (1, 5, '2006-12-5 11:18:00 GMT'); -- 4
insert into pinpoint (agent, location, datetime) values (2, 1, '2006-12-3 22:01:00 GMT'); -- 5
insert into pinpoint (agent, location, datetime) values (3, 3, '2006-12-7 02:10:00 GMT'); -- 6

insert into trip (departure, arrival) values (4, 1); -- covert move 1
insert into trip (departure, arrival) values (2, 5); -- covert move 2
insert into trip (departure, arrival) values (3, 6); -- covert move 3

EDIT: (I have not tested the syntax and it's written rather Postgres-centric.)

dtw · 2006-08-18 16:03:49

Phrak: more like

phrakture · 2006-08-18 17:02:59

so you want query like:

select main.id1, a.value, main.id2, b.value
from FirstTable as main
inner join SecondTable as a on main.id1 = a.id
inner join SecondTable as b on main.id2 = b.id

dtw · 2006-08-19 07:48:43

I fixed it. I had a spurious outer join according to access. The error message simply suggested i split my query in two and combine them - that worked a treat. Takes an age to run though!

Arch Linux

#1 2006-08-18 11:34:31

Schoolboy database creation error?

#2 2006-08-18 14:59:12

Re: Schoolboy database creation error?

#3 2006-08-18 15:07:27

Re: Schoolboy database creation error?

#4 2006-08-18 15:15:38

Re: Schoolboy database creation error?

#5 2006-08-18 15:30:12

Re: Schoolboy database creation error?

#6 2006-08-18 16:03:49

Re: Schoolboy database creation error?

#7 2006-08-18 17:02:59

Re: Schoolboy database creation error?

#8 2006-08-19 07:48:43

Re: Schoolboy database creation error?

Board footer