Query assembly query with INNER JOIN

Question

Query assembly query with INNER JOIN

Navigation

#1 by (1 votes)
#2 by (-1 votes)

0

Hello ...

I have the following query:

$conexao = connect();

$consulta3 = mysql_query("SELECT meu.id, f.idfollowed, eu_sigo.fname, eu_sigo.id, eu_sigo.profile, eu_sigo.photoperf, p.id, p.title, p.link, p.description, p.descr, p.url, p.photo, p.visivel, p.type, p.description, p.data
FROM u636623377_users AS meu
INNER JOIN u636623377_follows AS f ON f.idfollower = meu.id
INNER JOIN u636623377_users AS eu_sigo ON f.idfollowed = eu_sigo.id
INNER JOIN u636623377_posts AS p ON p.userid = f.idfollowed
WHERE meu.id = '$idperf'"); # id do usuário logado

This query picks up all user posts I follow, along with some more information about them, such as name, photo, and profile link that are stored in another table. This is working perfectly. However, it only returns the posts of the users I follow. I wish she would return my posts as well. What should I implement to make this happen?

Table structure:

u636623377_follows = pastebin. com / i4QfJBBH // join the spaces

u636623377_posts = pastebin. com / tMUB5t5E // join the spaces

u636623377_users = pastebin. com / sabLHx97 // join the spaces

php mysql sql join

asked by anonymous 29.12.2014 / 19:46

2 answers

How to change the focus of the webcam during stream? How to access the url of the current tab

score 1 · Answer 1

Your response is in UNION . You can briefly insert other queries in the return but they should return the same number of columns being of the same type.

Concatenate the following excerpt:

UNION    

SELECT meu.id    
     , NULL -- f.idfollowed    
     , NULL -- eu_sigo.fname    
     , NULL -- eu_sigo.id    
     , NULL -- eu_sigo.profile    
     , NULL -- eu_sigo.photoperf    
     , p.id    
     , p.title    
     , p.link    
     , p.description    
     , p.descr    
     , p.url    
     , p.photo    
     , p.visivel    
     , p.type    
     , p.description    
     , p.data    
FROM u636623377_users AS meu    
INNER JOIN u636623377_posts AS p ON p.userid = meu.id    
WHERE meu.id = '$idperf'

As your posts are, it does not make sense to return information from followers, and for this reason, we enter a neutral value in these columns, in this case NULL.

UPDATE

I left comments next to NULLs to see the columns we are dealing with. If you want to remove them, feel free.

score -1 · Answer 2

Try to include another join in the posts table to get your posts,

$conexao = connect();

$consulta3 = mysql_query("SELECT meu.id, f.idfollowed, eu_sigo.fname, eu_sigo.id, eu_sigo.profile, eu_sigo.photoperf, p.id, p.title, p.link, p.description, p.descr, p.url, p.photo, p.visivel, p.type, p.description, p.data
FROM u636623377_users AS meu
INNER JOIN u636623377_follows AS f ON f.idfollower = meu.id
INNER JOIN u636623377_users AS eu_sigo ON f.idfollowed = eu_sigo.id
INNER JOIN u636623377_posts AS p ON p.userid = f.idfollowed
INNER JOIN u636623377_posts AS meusposts ON meusposts.userid = meu.id
WHERE meu.id = '$idperf' ORDER BY p.data DESC"); # id do usuário logado