How to select multiple id from same table

Question

Here is the simplified version of my problem. I have these tables:

Master table

id - name - surname - fathers name - mothers name

Names

id - name

Surnames

id - surname

---

I want to query master table but I want to get id's for name, father's name and mother's name from other tables. I want something like;

SELECT 
    'id from names table that matches record name' 
    , 'id from surnames table that matches record surname' 
    , 'id from names table that matches record fathers name' 
    , 'id from names table that matches record mothers name'
FROM master_table

My problem is that I don't know how to join same table multiple times.

Answer 1

You can "join the same table multiple times" by aliasing the table in the FROM statement e.g. SELECT xyz FROM names AS n1 (where n1 is the alias for the table [names]). You can refer to the same table as many times as you want by using a different alias for the same table and it will be treated as a 'different' set of data.

You will then need to join between the various aliases to get the conditions you want such as "surnames that matches record surname".

As stated by a commenter above, the structure isn't very clear but you would end up with something like:

SELECT name, s1.surname, s2.surname from names AS n1
JOIN surnames AS s1 ON n1.mothersname = s1.surname
JOIN surnames AS s2 ON n1.fathersname = s2.surname

I'm sure the join isn't exactly this as you seem to have "intermediate" tables for name and surname (and that may be a design issue in itself) but the above should illustrate how to use the same table multiple times in a query.