SQL Self-reference join query not showing all entries

Question

there I have this table 'customer' that self-reference. The table looks like this

customer
---------
id_cus    name     sex      id_cus_family    family_as
001       A        M        002              son
002       B        F        001              mother
003       C        M        002              husband
004       D        M        003              father

id_cus_family is made to reference id_cus. I tried to query showing all entries with adding the name of their family name as new column. I used this:

SELECT
  c1.* , c2.name AS family_name 
FROM 
    customer c1
    inner join customer c2 on c2.id_cus = c1.id_cus_family

But the result didn't show all entries from customer table. The result went like this:

query result
---------
id_cus    name     sex      id_cus_family    family_as   family_name
001       A        M        002              son         B
002       B        F        001              mother      A
002       B        F        001              mother      C
003       C        M        003              husband     D

It should've show all result, right? or something wrong with my query code? I really appreciate the help.

Answer 1

You can use Left outer join

SELECT
    c1.* , c2.name AS family_name 
FROM 
    customer c1
left outer join customer c2
    on c2.id_cus = c1.id_cus_family

Answer 2

I have modified your query like this:

SELECT c1.*,c2.name as family_name 
FROM Customer as c1  
JOIN Customer as c2 ON c1.id_cus_family=c2.id_cus

The result will be like this:

id_cus  name    sex id_cus_family   family_as   family_name
1        A      M    2                son       B
2        B      F    1                mother    A
3        C      M    2                husband   B
4        D      M    3                father    C