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
[email protected]
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
>
>