SQL query with JOIN (task)
There is a task:
A father always has only one son. Sons, in turn, can also be fathers. Requests:
(1) get the user together with his father and son,
(2) get the user's grandfather,
(3) get the user's great-grandfather.
All data is in one table users: id, name, sons_id
For one request, I get to get the father and son, together for some reason, the numbering starts to fly off and everyone gets confused, grandfather and great-grandfather can not get at all it turns out.
Help me figure out how to get it all from one table, combine several and get queries from them, the task is to get everything from one table.
This is what my request looks like:
SELECT sons.name as son, father.name as father
FROM users
INNER JOIN users as sons ON users.son_id = sons.id
INNER JOIN users as father ON father.son_id = users.id
But I get the wrong result, the cells are connected to the wrong IDs with which it was intended, from which the conclusion is that I am doing something wrong, I could not understand what is wrong myself.
1 answers
The first thing that obviously catches your eye is that the query will return only those users who have both a father and a son, that is, probably about a third of the table.
The case in the internal join of tables, that is, in INNER JOIN. If there is no matching pair for the connection, say users.son_id and sons.id, then the user will not be included in the result.
You can use external connections, in which, even if there is no pair, instead of the name of the son or father, the SQL server returns NULL. External connections, unlike internal ones, are asymmetric - one of the two tables will be shown in full, and the second one will be attached to it.
A query with the left external connection will return the names of all users, but if they do not have a father or son, we will see NULL in this place.
SELECT users.name AS userName, sons.name AS sonName, fathes.name AS fatherName
FROM users
LEFT OUTER JOIN users AS sons ON users.son_id = sons.id
LEFT OUTER JOIN users AS fathers ON fathers.son_id = users.id
Instead of LEFT OUTER JOIN , you can simply write LEFT JOIN.
The grandfather is obtained by a double connection.
SELECT users.name AS userName, grandfathers.name AS grandfatherName
FROM users
JOIN users AS fathers ON fathers.son_id = users.id
JOIN users AS grandfaghers ON grandfathers.son_id = fathers.id
Here JOIN means INNER JOIN. So we will get all the users who have there is definitely a grandfather in the table. Replacing JOIN with LEFT JOIN, we get all the users in the table, with the names of the grandfathers, if there are any, and NULL, if there are none.
The great-grandfather is obtained by a triple compound.
SELECT users.name AS userName, grandgrandfathers.name AS grandgrandfatherName
FROM users
JOIN users AS fathers ON fathers.son_id = users.id
JOIN users AS grandfaghers ON grandfathers.son_id = fathers.id
JOIN users AS grandgrandfaghers ON gramdgrandfathers.son_id = grandfathers.id