How to print data from one table, if it does not exist in another table (php mysql)

Question

I have two tables comments and comments_liked. Users (viewer_id) can like comments written by (UID). Comments are stored in the comments table and comments that are liked are stored in the comments_liked table (as shown in the image attached). **What Im trying to do:

When a user that has (viewer_id) as their id, found in the comments_liked table, is viewing their profile, they should see any comments that they have liked/given to a user (UID), Only when the comment does not exist in the comments table where uid = uid

Please don't get confused, as I am not trying to show all comments where uid=uid.

Below is my code in php

please note : if we can not say where comments_liked.uid != comments.uid as that would show all other values in the comments table.

$query = "
    SELECT * FROM comments_liked

    INNER JOIN comments ON uid = comments.uid

    WHERE uid = ? 
    AND viewer_id = ? 
";

but my code only prints the records where commnets_liked.uid = comments.uid which is the opposite of what I want.

so if I am user 1, I need to see the record from comments_liked where viewer_id = 1, as it does not exist in comments table.

THANKS FOR YOUR HELP!

This is a faq. Google your title & tags. Read about SQL EXCEPT or (since it isn't in MySQL) idoms via LEFT JOIN, NOT IN & NOT EXISTS. You have already tagged this with left-join so you must have some idea about this. And you can't think you are the first person to ask this. PS As an answer says, you probably want to record that a certain user liked a certain comment, not that a they liked some commment(s) of a certain other user. Also [re querying](https://stackoverflow.com/a/33952141/3404097). — philipxy, Apr 16 '18 at 21:56
Hi. Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. PS Learn about SQL table aliases. And joins. — philipxy, Apr 16 '18 at 22:01
@philipxy - thanks you make some good points. I appreciate the help and showing me the answer at another post — mark, Apr 18 '18 at 03:27
I cut & pasted those comments from a list as I do on endless questions. The more you read & act on the links starting at [help]--including especially making the effort to google & to organize & write clearly--the better you will get answers--whether you end up asking or not. Good luck. — philipxy, Apr 18 '18 at 03:32

score 1 · Answer 1 · edited Mar 25 '19 at 20:41

You will need to know which comment a like is for.

In this case, the comments_liked table is about comments, so it shouldn't (at least not necessarily) contain the uid of the user who posted the comment, but instead, it should have the comment_id of the comment itself.

After all, if user 100 has five comments, and user 200 liked only one of those comments, you only want to see that one comment, not all five.

Once you have that, you can select all the comments that a user liked like this:

SELECT
  c.comment,
  authors.username AS comment_author
FROM
  comments_likes cl
  INNER JOIN comments c ON c.id = cl.comment_id
  INNER JOIN users authors ON authors.id = c.uid
WHERE
  cl.viewer_id = ?

So as you can see:

First of all it depends on you changing the model, so you know which comment a like is for.
Then you can join the content to get its content.
Then you can join the user table to get the author of the comment (you probably want to display that too). This can be used from the comment table, so you don't need to have that uid in the comment_likes table.
I used aliases for the tables. This may help you get some overview in the query, because an alias can be shorter (cl) or can contain some context (authors, indicating that these users are authors in the context of the query).
I used an alias for the column as well, for the same reason.

Hmm, thank! you make some good points. I see what you mean. that could be a problem, thanks — mark, Apr 16 '18 at 09:48
Just one problem though, while you made good points, my main objective is still to show only records in comment_likes that dont exist in the comments table but only the comments like table. So i dont want to show comments.uid = comments_like.uid. if that makes sense — mark, Apr 16 '18 at 10:00

How to print data from one table, if it does not exist in another table (php mysql)

1 Answers1