I want to use sql to query out the posts I have posted and the posts posted by the users I follow. How should I write this sql? - Codes Helper - Programming Question Answer

I want to use sql to query out the posts I have posted and the posts posted by the users I follow. How should I write this sql?

select t.*
from talk t
where t.user_id = 2
Union
select t.*
from talk t, user_contact c
where c.user_id = 2 and c.contact_type = 1 and c.be_user_id = t.user_id

you can write it this way, but you don"t want to write it this way. You want to use an inner connection or a left connection

.

there is a situation where the user has no data in the user_contact table, which means he is not paying attention to anyone

user_id = 1 is the user"s id, that is, my id

select t.*
from talk t left join user_contact c on c.user_id = t.user_id
where t.user_id = 1 or (c.user_id = 1 and c.be_user_id = t.user_id and c.contact_type = 1)

I don"t understand what"s wrong. Why is there duplicate data

?

I write the query like this and there is duplicate data

user_contact is the user relation table

talk is the post table

posts table talk has the user_id of the user who posted the post
then the user relations table user_contact stores that user_id,be_user_id is the followed user"s id,contact_typ_type is the user relationship type, 1 is the follow type

ask for advice. I don"t know how to write

.

Java sql mysql spring

Jun.25,2021

select * from talk 
where user_id=1 
or user_id in (
select be_user_id from user_contact where user_id=1 and contact_type = 1
)

talk and user_contact have a many-to-many relationship! !
take a chestnut: user_id = 1 posted m posts, and he followed n people, so there will be at least m records in the left join you wrote, using Cartesian product, in fact, you only need m posts, add a group by.

select t.*
from talk t left join user_contact c on c.user_id = t.user_id
where t.user_id = 1 or (c.user_id = 1 and c.be_user_id = t.user_id and c.contact_type = 1)
group by t.user_id

however, when you look at the left join condition above and the or condition after where, you will find c.user_id = t.user_id = 1! what do you mean, that is, the result of the association still shows the posts of user 1, and does not relate the posts of the people he follows.
the correct one should be:

select t.*
 from talk t , user_contact c
 where t.user_id = 1 or (c.user_id = 1 and c.be_user_id = t.user_id and c.contact_type = 1)
 group by t.user_id

this is modified according to what you wrote. In terms of efficiency, it's better to write that way.

Previous: Transform causes border-radius,overflow:hidden to fail?

Next: Laravel sub-table sava saves data bug

Menu