Re: joining tables
| От | Patrick Headley |
|---|---|
| Тема | Re: joining tables |
| Дата | |
| Msg-id | 6194a03b-17ab-b9cf-f65e-562352cd762a@linxco-inc.com обсуждение исходный текст |
| Ответ на | joining tables (TedJones <ted@mentra.co.uk>) |
| Ответы |
Re: joining tables
(TedJones <ted@mentra.co.uk>)
|
| Список | pgadmin-support |
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.
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.
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
В списке pgadmin-support по дате отправления: