SQL query not equal to operator not working, with JOIN

not equal to in sql
can we use not equal to in join condition
sql not in
sql not equal to multiple values
sql joins
sql not equal string
sql not equal to null
left join

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.

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.

Not Equal in Inner Join not working – SQLServerCentral, For getting records present in Table2 but not in Table1,. you need to join these two tables using left outer join as: Select A.*. from Table2 A left  This time query took less time to return the same number of rows. It took only 1 ms while query with SQL Not Equal took 52 ms. In the Actual Execution plan, it used Clustered Index Seek while SQL Not Equal used. In the property for the Clustered Index Seek, it uses an equality operator to produce a similar result set.

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 <=>).

Not Equal Operator, Learn about Not Equal Operator, a type of Comparison Operator and its uses in (t2.x, t2.y) FROM t1 INNER JOIN t2; SELECT (t1.a != t2.x) OR (t1.b != t2.y)  With that, I'd probably use either the not exists or an outer join to solve this problem. Or, seeing as this is SQL Server 2008, the following: with ExceptCTE as (

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);

Basic and Complex Uses of Not Equal Comparison Operator in T-SQL, This article is focused on the T-SQL Not Equal comparison operator () and its SQL Server 2008 and upper versions; Azure SQL Database; Azure SQL Data of T-SQL and are capable of writing and running basic SQL scripts. we need to see the categories with articles by using INNER JOIN as follows: If you want to get values from both tables, you can use full outer join and take records which have one side as null: select a.*, b.* from tableA a full outer join tableB b on a.col = b.col where a.col is null or b.col is null. Obviously this way all the values for either one table or the other will be null. Share a link to this answer.

<> (Not Equal To) (Transact-SQL), Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal  Less Than or Equal To (<=) Operator. The sql Less Than or Equal To operator is used to check whether the left-hand operator is lower than or equal to the right-hand operator or not. If left-hand operator lower than or equal to right-hand operator then condition will be true and it will return matched records.

MySQL not equal to operator, Syntax: <>, != MySQL Version: 5.6. Example: MySQL not equal to (<>) SELECT pub_name,country,pub_city,estd FROM publisher WHERE  In almost all cases when we use the <> operator (or any other operator in conjunction with the NOT operator, i.e.. NOT IN) index seeks will not be performed and instead a table/index scan is required. For this example let's make an update to one of our test tables to skew the data a little. We'll also add an index to the table on the column

NULL -Aware Comparison: is [not] distinct from, With is [not] distinct from SQL also provides a comparison operator that treats two to work on more databases by using select without from instead of the values  Nevertheless, you need to be cautious when using the NOT IN operator if the subquery’s source data contains NULL values. If so, you should consider using a NOT EXISTS operator instead of NOT IN, or recast the statement as a left outer join.

Comments
  • It should work fine with != or <>. Maybe look at when to use quotes? Probably also want to look at proper escaping / PDO.
  • 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.
  • What is the column type for userid and hidden?
  • Sample data and desired results would really help.
  • Thanks everyone! I have posted an update to the question with tables and outcome!
  • Thanks! Managed to get it working with a few tweaks! Switched user_id and est_id.
  • Also... user_id should look like userid.
  • Thanks for the help! Unfortunately I still can't get it to work! I have added some more details to the question!