how to JOIN on not null Im trying to create referals system. I am doing that by passing into during database every registaration all referals (first, second and third "stage").

to clarify, if user ID 3 registers from ref. of user ID 2, and user ID 2 was registered from ref of ID 1, the values for user ID 3 would look like:

`r1` = 2, `r2` = 1, `r3` = NULL


To read from database who is registered using some id's ref link with whole path, I figured out this query:

SELECT `sub`.`id` AS first,`sub2`.`id` AS `second`,`sub3`.`id` AS `third`

FROM `users` AS `usr` 

JOIN `users` AS `sub` ON `sub`.`r1` = `usr`.`id` 
JOIN `users` AS `sub2` ON `sub2`.`r2` = `usr`.`id` 
JOIN `users` AS `sub3` ON `sub3`.`r3` = `usr`.`id`

WHERE `usr`.`id` = :CurrentUserId ;


(:CurrentUserId is PDO placeholder for PHP for int value)

Although, its problematic, as it works only for accounts with all 3 refers into past, so people with only first line or first and second line, but without third will not be listed. In other words, that would mean, if there is user, that has following values

r1 = 2, r2 = 1, r3 = NULL


is not listed. It works if r3 is equal to something.

I could work it around by sending 3 queries to database, but I am sure, that there is better way.

(Im not very expirienced with databases)
Use left joins instead of inner joins so that records from users will still display even if r1, r2, and r3 are null:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.r1 = usr.id 
LEFT JOIN users AS sub2 ON sub2.r2 = usr.id
LEFT JOIN users AS sub3 ON sub3.r3 = usr.id

WHERE usr.id = :CurrentUserId ;


However, based on your example I think you may need the following query instead, changing the join conditions so that sub, sub2, and sub3 represent referred users:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.id = usr.r1
LEFT JOIN users AS sub2 ON sub2.id = usr.r2
LEFT JOIN users AS sub3 ON sub3.id = usr.r3

WHERE usr.id = :CurrentUserId ;