The first thing I noticed is that there is no primary key field on
Names. If Friend is a child table then you will use a LEFT OUTER JOIN.
Friend will need a foreign key field that has values matching the
primary key field in Names. Friend should also have it's own primary key
field.
If you are storing names and friends in the same table then you should
join them using a junction table. A junction table will have the unique
ID value of the name record and the unique ID value of the friend record
within the same record. A junction table allows for many-to-many
relationships. In other words, you could build a tree of friend
relationships.
Where you want to display yes/no values, if you use a LEFT OUTER JOIN
and there is no related friend you will get the Name record back and an
empty Friend record. If there is a friend record you will get the Name
and the Friend record back. If there is more than one friend you will
get one Name record per Friend record.
If you really just want yes/no values, you can use a sub query instead
of a JOIN within the main query that selects the count of Friend records
related to each Name record. If the count = 0, there are no Friend
records. If the count is greater than 0, there are Friends. A CASE
statement can convert the count to yes/no.
The primary key field on each table should be unique to that table and
have no other meaning. For example, you shouldn't use email or telephone
as primary key fields because of the chance that someone else my
eventually have the same email address or telephone number as the person
you've already stored in your database. Another good reason for primary
keys is if you want to create updatable views. Without primary keys the
database engine can't determine which records you are working on.
A primary key field can be an auto incrementing number (sequence or
identity). It can also be a timestamp or UUID. The problem with
timestamp and UUID is the slight possibility of a duplicate value.
Additionally, numeric keys process faster than text keys.
The other thing about your sample is why Details is separate from Names.
Things that could have multiple records per name record should be in
separate tables. Examples would be emails, addresses and phone numbers
(work, home, etc.). Additional details for a person should be stored
with the person unless the same field could have more than one value.
*/Patrick Headley/*
Linx Consulting, Inc.
(303) 916-5522
phead...@linxco-inc.com
www.linxco-inc.com
On 9/2/19 11:24 AM, TedJones wrote:
I'm having problems joining 3 tables to provide a 4th table in the correct
format. I believe I need a FULL OUTER JOIN but does not give the result that
I require.See below:
Example 1
Table: Names Table: Friend
Name Tel email PostCode Name friend email
Jim 1 aa I Dave yes cc
Ted 2 bb J Will yes dd
Dave 3 cc K Zac yes ff
Will 4 dd L Byron yes gg
Table: Details
Name email Town PostCode
John ee A M
Zac ff B N
Byron gg C O
Will dd D L
Gary hh E P
Ted bb F J
Combined table: Result
Name Tel email Town PostCode friend
Jim 1 aa I
Ted 2 bb F J
Dave 3 cc K yes
Will 4 dd D L yes
John ee A M
Zac ff B N yes
Byron gg C O yes
Gary hh E P
Example 2
Table: Names Table: Friend
Name Tel email PostCode Name friend email
Jim 1 aa I Gareth yes ii
Ted 2 bb J Tony yes jj
Dave 3 cc K Ken yes kk
Will 4 dd L Lloyd yes ll
Table: Details
Name email Town PostCode
John ee A M
Zac ff B N
Byron gg C O
Gary hh E P
Combined table: Result
Name Tel email Town PostCode friend
Jim 1 aa I
Ted 2 bb J
Dave 3 cc K
Will 4 dd L
John ee A M
Zac ff B N
Byron gg C O
Gary hh E P
Gareth ii yes
Tony jj yes
Ken kk yes
Lloyd ll yes
--
Sent from:
https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html
