Join 4 tables in SQL query

Question

I have 4 relationship tables in my database and you want to join them to get the general value ... for example, I have friends, relatives, acquaintances and acquaintances, and you want to join them to get the value "ALL" .

The format of the table is as follows:

Friends

id follower following --------------------- 1 2 3 2 4 5 

A family

 id follower following --------------------- 1 5 6 2 7 8 

After

 id follower following --------------------- 1 9 10 2 11 12 

Acquaintances

 id follower following --------------------- 1 13 14 2 15 16 

The correct query to join all 4 tables ...

 SELECT following FROM friends INNER JOIN family ON friends.following=family.following INNER JOIN following ON friends.following=following.following INNER JOIN acquaintances ON friends.following=acquaintances.following WHERE follower='id' 

Basically, I want to join and get the "next" value from all four tables, where id = my id

Answer 1

In the current query, only the result will be displayed if all tables have a link with their friends table. I believe that you are more looking for something like this.

 SELECT following FROM friends WHERE follower='id' UNION ALL SELECT following FROM family WHERE follower='id' UNION ALL SELECT following FROM following WHERE follower='id' UNION ALL SELECT following FROM acquaintances WHERE follower='id' 

or a little better read and easier to tune due to some performance

 SELECT following FROM ( SELECT following, follower FROM friends UNION ALL SELECT following, follower FROM family UNION ALL SELECT following, follower FROM following UNION ALL SELECT following, follower FROM acquaintances ) AS f WHERE follower='id' 

UNION

UNION is used to combine the result of several SELECT statements into a single result set.

Answer 2

You will want to do this:

 SELECT following FROM friends WHERE follower='id' UNION SELECT following FROM family WHERE follower='id' UNION SELECT following FROM followers WHERE follower='id' UNION SELECT following FROM acquaintances WHERE follower='id' 

What will give

 following --------------------- 3 4 6 8 10 12 14 16 

It might be better to stick with friends, relatives, followers, and acquaintances in the same table, although the differences between these categories will not be large enough to merit their own tables.

Answer 3

Not really. If I simplify my example to friends and relatives, joining them will return only people who are friends and family, and allow you to extract identifiers from all tables in one row, which is practically useless.

What you want is union, as another answer shows. However, the best choice might be to have one relationship table, which will have a special column (enum?), To determine its type, and not one table for each kind of relationship.

Answer 4

as indicated in my commentary on this situation, and depending on the amount of data that you have in your x-tables, I would use union:

 (SELECT following FROM friends WHERE follower='id') UNION (SELECT following FROM family WHERE follower='id') UNION (SELECT following FROM following WHERE follower='id') UNION (SELECT following FROM acquaintances WHERE follower='id') 

Answer 5

You must reverse engineer the entity table. friends, relatives, acquaintances can be divided by fields

 id follower following type --------------------------------- 1 5 6 Family 2 7 8 Friends 3 13 14 Acquaintance 4 15 16 Friends 

then you can filter, organize or group type