Re: joining tables

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
pheadley(at)linxco-inc(dot)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
>
>