Get all rows from one table with JOIN

[ebolt007]

I think I need to use Join for this?

I have 2 tables, where I have a bunch of messages, and basically on each message I have "hide" button that posts the UserID, PostID, and Hidden as 1 in another table.

So my hidden table has 3 columns: UserID, PostID, Hidden

My messages table has all that are shown below, but I need to get all the PostIDs from "messages" except the ones that have a "1" under Hidden in the Users_WallPosts_Hide table. I suck at Joins, and can't figure this. Any help would be appreciated. Thanks

 

 

$query = mysql_query("      
      SELECT messages.msg_id, messages.uid_fk, messages.message, messages.ToID, messages.created, messages.uploads 
      FROM messages
                    
                    INNER JOIN Users_WallPosts_Hide ON messages.msg_id = Users_WallPosts_Hide.PostID
                    WHERE Users_WallPosts_Hide.Hidden = '1'


       order by msg_id desc") or die(mysql_error());

[Barand]

Since you will want to show messages that do not have record in the other table you need a LEFT JOIN

 

Try

 

SELECT messages.msg_id, messages.uid_fk, messages.message, messages.ToID, messages.created, messages.uploads 
FROM messages
    LEFT JOIN Users_WallPosts_Hide 
        ON messages.msg_id = Users_WallPosts_Hide.PostID
        AND Users_WallPosts_Hide.Hidden = '1'
WHERE Users_WallPosts_Hide.PostID IS NULL
ORDER BY messages.msg_id DESC

[ebolt007]

That almost worked, but I am also doing to other Joins and using UNION to put them together, so now it is doubling up all of my messages that are coming in, altho it only shows one of the hidden ones, so it's hiding one, but I can't figure out why it's doubling up now that I have added it to my other query like below? Any ideas?

 

	    $query = mysql_query("
      SELECT messages.msg_id, messages.uid_fk, messages.message, messages.ToID, messages.created, messages.uploads 
      FROM messages 
      WHERE messages.uid_fk='$uid' 
      
      UNION
                    SELECT messages.msg_id, messages.uid_fk, messages.ToID, messages.message, messages.created, messages.uploads 
                    FROM messages 
                    INNER JOIN Users_Friends ON messages.uid_fk = Users_Friends.UserID
                    WHERE Users_Friends.FriendID = '$uid' AND Users_Friends.Level = '1' 
                    
      UNION
                    SELECT messages.msg_id, messages.uid_fk, messages.ToID, messages.message, messages.created, messages.uploads 
                    FROM messages 
                    INNER JOIN Users_Friends ON messages.uid_fk = Users_Friends.FriendID
                    WHERE Users_Friends.UserID = '$uid' AND Users_Friends.Level = '1' 
             
      UNION
                    SELECT messages.msg_id, messages.uid_fk, messages.message, messages.created, messages.ToID, messages.uploads 
                    FROM messages 
                    LEFT JOIN Users_WallPosts_Hide ON messages.msg_id = Users_WallPosts_Hide.PostID AND Users_WallPosts_Hide.Hidden = '1'
                    WHERE Users_WallPosts_Hide.PostID IS NULL     

                     order by msg_id ") or die(mysql_error());
                    

[jazzman1]

Try,

 

$query = "
SELECT m.msg_id, m.uid_fk, m.message, m.ToID, m.created, m.uploads 
      FROM messages m 
      JOIN Users_WallPosts_Hide w ON m.msg_id = u.PostID
      JOIN Users_Friends f ON m.uid_fk = f.FriendID
      WHERE w.Hidden IS NULL OR f.Level = 1
      ORDER BY m.msg_id DESC
";

[jazzman1]

A little mistake,

 

u.PostID
to 
w.PostID

 

Sorry about that

[ebolt007]

Nope, unfortunately that didn't do the trick either, it didn't display anything until I modified it a bit like below, I had to use the INNER JOIN for the User_WallPosts_Hide part and then also had to ad the Friend part with UserID because it has to look at 2 different ID to see if Friends are Level 1 (UserID and FriendID)

This made it display it 6 times tho per message, and I have 2 messages in the hidden part, and when I take out the FriendID part then it displays 4 of them, so it's still doubling up, but doubling up everything now. Below is what I have.

 

SELECT m.msg_id, m.uid_fk, m.message, m.ToID, m.created, m.uploads 
      FROM messages m 
      LEFT JOIN Users_WallPosts_Hide w ON m.msg_id = w.PostID
       JOIN Users_Friends f ON m.uid_fk = f.FriendID
       JOIN Users_Friends j ON m.uid_fk = j.UserID
      WHERE w.Hidden IS NULL AND m.uid_fk='$uid' OR f.Level = 1 AND m.uid_fk='$uid' OR j.Level = 1 AND m.uid_fk='$uid' 
      ORDER BY m.msg_id DESC limit 10 

[fenway]

You're missing AND and OR in your WHERE clause without parentheses.

[ebolt007]

Hmmm, can you elaborate on that a bit? I tried the below with parentheses and missing single quotes on the LEVEL=1 and get the same results. The 2nd one gives me 2 returns, the first one gives me 4 returns on each query.

 

4returns:

SELECT m.msg_id, m.uid_fk, m.message, m.ToID, m.created, m.uploads 
      FROM messages m 
      LEFT JOIN Users_WallPosts_Hide w ON m.msg_id = w.PostID
       JOIN Users_Friends f ON m.uid_fk = f.FriendID
       JOIN Users_Friends j ON m.uid_fk = j.UserID
      WHERE ((w.Hidden IS NULL AND f.Level = '1' AND m.uid_fk='$uid') OR (w.Hidden IS NULL AND j.Level = '1' AND m.uid_fk='$uid')) 
      ORDER BY m.msg_id DESC limit 10

 

2 returns:

 

SELECT m.msg_id, m.uid_fk, m.message, m.ToID, m.created, m.uploads 
      FROM messages m 
      LEFT JOIN Users_WallPosts_Hide w ON m.msg_id = w.PostID
       JOIN Users_Friends f ON m.uid_fk = f.FriendID
      WHERE (w.Hidden IS NULL AND f.Level = '1' AND m.uid_fk='$uid') 
      ORDER BY m.msg_id DESC limit 10

[Barand]

Hmmm, can you elaborate on that a bit?

 

Consider the following table "test"

+------+------+------+

| A    | B    | C    |

+------+------+------+

|    1 |    1 |    1 |

|    1 |    1 |    0 |

|    1 |    0 |    1 |

|    1 |    0 |    0 |

|    0 |    1 |    1 |

|    0 |    1 |    0 |

|    0 |    0 |    1 |

|    0 |    0 |    0 |

+------+------+------+

 

If we now run the query

"SELECT A,B,C FROM test WHERE (A AND B) OR C"

we get

+------+------+------+

| A    | B    | C    |

+------+------+------+

|    1 |    1 |    1 |

|    1 |    1 |    0 |

|    1 |    0 |    1 |

|    0 |    1 |    1 |

|    0 |    0 |    1 |

+------+------+------+

5 rows in set (0.00 sec)

 

On the other hand

SELECT A,B,C FROM test WHERE A AND (B OR C)

+------+------+------+

| A    | B    | C    |

+------+------+------+

|    1 |    1 |    1 |

|    1 |    1 |    0 |

|    1 |    0 |    1 |

+------+------+------+

3 rows in set (0.00 sec)

 

So the position of the parentheses determines the logic.

 

If you run the query without any parentheses you get the same results as the first query, but the logic is not as clear.

[ebolt007]

I still don't get it. I've tried every parenthesis logic possible and still always get 2 returns.

[ebolt007]

Even doing like below gives me 2 returns, with no AND's OR's just querying the "message msg_id" So I don't think it's parenthasis.

 

SELECT m.msg_id, m.uid_fk, m.message, m.ToID, m.created, m.uploads 
      FROM messages m 
      LEFT JOIN Users_WallPosts_Hide w ON m.msg_id = w.PostID
       JOIN Users_Friends f ON m.uid_fk = f.FriendID
       JOIN Users_Friends j ON m.uid_fk = j.UserID
      WHERE m.uid_fk='$uid' 
      ORDER BY m.msg_id DESC limit 

[fenway]

Then you're going to have to provide table definitions and sample data -- which, by the way, is requested & required by the posting guidelines.