Inner join only if a field is not empty

Question

I could have done what I want with two request, but I want to make it with only one.

In fact, I have 4 tables with :

Table 1 : id, sub-id
Table 2 : id, sub-id
Table 3 : id, login
Table 4 : id, login

I make a request like that :

SELECT Table1.id, Table1.sub-id, Table2.id, Table2.sub-id, 
   Table3.login, Table4.login FROM Table1
   INNER JOIN Table2 ON (Table1.id = Table2.id AND Table1.sub-id = Table2.sub-id)
   INNER JOIN Table3 ON (Table3.id = Table1.id)
   INNER JOIN Table4 ON (Table4.id = Table1.id)
   WHERE Table1.id = "my_id" AND Table1.sub-id = "my_subid"

I want to join Table3 only if id is not empty, if it is empty, I join Table4.

Do you have any ideas please ?

I've heard that a left join could help, but I'm not that accustomed to these keywords so ...?

Answer 1

Like said in the other answers, you can use a left join. You can also add case statement to have only one login column :

SELECT 
    Table1.id, 
    Table1.sub-id, 
    Table2.id, 
    Table2.sub-id, 
    CASE 
        WHEN Table3.id IS NOT NULL THEN Table3.login
        ELSE Table4.login
    END CASE AS login
FROM Table1
INNER JOIN Table2 ON (Table1.id = Table2.id AND Table1.sub-id = Table2.sub-id)
LEFT JOIN Table3 ON (Table3.id = Table1.id)
LEFT JOIN Table4 ON (Table4.id = Table1.id)
WHERE Table1.id = "my_id" AND Table1.sub-id = "my_subid"