SQL query not equal to operator not working, with JOIN

Question

I wonder if someone could help me with an SQL query.

I am trying to display all results except where in Table 2 both the userid column equals 1 and the hidden column equals 1.

I am basically trying to hide results from individual users based on their id and the value in the hidden column either 1 or empty.

I have so far managed to make a query that does the opposite and can't figure out how to change it. I have tried adding !=, <>, NOT and a few other things, but nothing is working for me!

Here is the query I am working with.

$stmt = $conn->prepare("SELECT tl.id, tl.name, tl.locale, uh.hidden 
  FROM theList AS tl 
  LEFT JOIN user_hidden_list AS uh ON uh.est_id = tl.id 
  WHERE uh.userid = '1' AND uh.hidden = '1'");

How do I display all results expect those with a uh.userid = 1 and uh.hidden = 1

UPDATE: Tables

Table: user_hidden_list

 userid | Hidden | est_id
---------------------------
   1    | 1      |   1
   2    | 1      |   1
   1    | 1      |   2

Table: theList

id |    name  | locale
------------------------
 1 |    Jacks   |   LDN
 2 |    MacD's  |   LDN
 3 |    BK      |   LDN
 4 |    Byron   |   LDN

So if I am logged in with and userid of:

1 I should see: BK and Byron.

2 I should see: MacD's, BK and Byron.

3 (or anything else) I should see: Jacks, MacD's, BK and Byron.

It should work fine with `!=` or `<>`. Maybe look at [when to use quotes](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql)? Probably also want to look at proper escaping / [PDO](https://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). — ficuscr, Oct 16 '18 at 20:52
Hint: When you reference a column from an outer-joined table, it turns it into an inner join. You can apply filters in the `ON` portion of your query for your outer-joined table. — RToyo, Oct 16 '18 at 20:53
Thanks everyone! I have posted an update to the question with tables and outcome! — tbowden, Oct 16 '18 at 21:06

Shidersz · Accepted Answer · 2018-10-17T02:55:37.793

If i understood correctly, the table user_hidden_list mantains a relation between one user and the users he can't see. So for, example, user with id equal to 1 (matching on column userid) can't see users 1 and 2 (matching on column est_id).

So, for a particular user with ID = X, we can get the ID list of user he can't see like next:

SELECT
    est_id
FROM
    user_hidden_list
WHERE
    userid = X AND hidden = 1;

Using the previous query, we can get the visible users for user X like on next query:

SELECT
    tl.id, tl.name, tl.locale
FROM
    theList AS tl
WHERE
    tl.id NOT IN (SELECT est_id
                  FROM user_hidden_list
                  WHERE userid = X AND hidden = 1);

I'm sure there will be a better (elegant) way to do this, but i'm just leaving work and my mind is not working nice now.

Thanks! Managed to get it working with a few tweaks! Switched user_id and est_id. — tbowden, Oct 16 '18 at 22:51

score 0 · Answer 2 · answered Oct 16 '18 at 20:54

I am trying to display all results except where in Table 2 both the userid column equals 1 and the hidden column equals 1.

This does not suggest an outer join. You seem to want:

SELECT tl.id, tl.name, tl.locale, uh.hidden 
FROM theList tl JOIN
      user_hidden_list uh
      ON uh.est_id = tl.id 
WHERE NOT (uh.userid = 1 AND uh.hidden = 1);

I am guessing that userid and hidden are numbers of some sort, so I removed the single quotes. If they are really strings, then use the single quotes.

This also assumes that these values cannot be NULL. If that is a possibility, then the logic can be adjusted (using the null-safe comparator <=>).

Thanks for the help! Unfortunately I still can't get it to work! I have added some more details to the question! — tbowden, Oct 16 '18 at 21:09

score 0 · Answer 3 · answered Oct 16 '18 at 21:57

0

Try out this, with even parantheses:

SELECT tl.id, tl.name, tl.locale, uh.hidden 
FROM theList tl JOIN
      user_hidden_list uh
      ON uh.est_id = tl.id 
WHERE (uh.userid <> 1 AND uh.hidden <> 1);

answered Oct 16 '18 at 21:57

Gratien Asimbahwe

1,606
4
19
30

SQL query not equal to operator not working, with JOIN

3 Answers3