Query in MySQL

Question

Query in MySQL

Navigation

#1 by (1 votes)

0

Good afternoon, I have a database your name is socialdb.

I have the following tables:

users (userid, name, email, password).

friends (friend_id, send_user, received_user, status)

posts (pub_id, id_user, comment, avatar, creative_date)

What do I need?

The user with id 4 sent the request to the user with the id 7, then the friends table looks like this:

friends (1, 4, 7, 1), how they can see id_amigo is an A_I, the field envio_user appears the user who sent the request in this case user 4, the field recibio_user shows the id of the user who received the request in this case user 7 and finally the status in 1, which means that they are friends because the request was accepted.

Well and now?

It turns out that the user with id 4 has sent and received requests, in one way or another his id will be repeated in envio_user or recibio_user, for which then I only need his id of friends suppose (7 - 25 - 21 - 90) . After this, I need to get all the publications information from the user_id the friends that resulted from the previous query.

Purpose: Limit publications that can only see those of their friends and not that of the general public.

mysql

asked by Andrés J Valencia 15.12.2017 в 19:06

source

1 answer

The type 'Servicios.WS1' is ambiguous View values of FormData

score 1 · Accepted Answer

Something like that could work:

SELECT * FROM publicaciones where usuario_id = 1
UNION
SELECT * FROM publicaciones where usuario_id in(
    SELECT envio_user from amigos where recibio_user = '1' AND estado ='1'
    UNION
    SELECT recibio_user from amigos where envio_user = '1' AND estado ='1'
)

Now someone more involved with sql can surely improve this query to make it more performant.