SQL select when it is different value

sql select unique values from multiple columns
sql select distinct values and count of each
distinct sql
sql select distinct multiple columns
select distinct on one column with multiple columns returned
sql select distinct count
sql count(distinct multiple columns)
sql distinct vs unique

I have two tables and I want to select row from table_2 with different values from row employ in table_1 to show a table with registered complaints without an employing number, I have tried this sql statement:

SELECT * FROM krita_db, sjofor_db WHERE employing_nr != nr ORDER BY id DESC

but get then 3 of the same row if the

+----+--------------+-------------------------------------+
| id | employing_nr |              complaint              |
+----+--------------+-------------------------------------+
|  1 |          123 | something bad                       |
|  2 |          333 | you have to do something with this  |
+----+--------------+-------------------------------------+

+----+-----+------+---------+----------+
| id | nr  | navn | adresse |   tlf    |
+----+-----+------+---------+----------+
|  1 | 123 | ola  | ---     | 12345678 |
|  2 | 321 | kari | ---     | 98765432 |
|  3 | 222 | gerd | ---     | 12344321 |
+----+-----+------+---------+----------+ 

I just want to show one of the same complaint and not 3 times, how can I accomplish that?

With my code now I get this table:

+----+--------------+--------------------------------------+
| id | employing_nr |              complaint               |
+----+--------------+--------------------------------------+
|  1 |          123 | something bad                        |
|  1 |          123 | something bad                        |
|  2 |          333 | you have to do something with this   |
|  2 |          333 | you have to do something with this   |
|  2 |          333 | you have to do something with this   |
+----+--------------+--------------------------------------+

I want to display this, the complaint with an employing_nr that is not registered:

+----+--------------+--------------------------------------+
| id | employing_nr |              complaint               |
+----+--------------+--------------------------------------+
|  2 |          333 | you have to do something with this   |
+----+--------------+--------------------------------------+

Try to avoid implicit joins in the query.

You can try EXIST operator as below

SELECT  * 
FROM krita_db 
WHERE NOT EXISTS (
  SELECT 1 FROM sjofor_db
  WHERE krita_db.employing_nr = sjofor_db.nr)

fiddle

Also, you can get same results using Hoàng Đăng's answer (LEFT JOIN + NULL check)

SQL SELECT DISTINCT Statement, pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

Try this

Select * FROM krita_db LEFT JOIN sjofor_db ON employing_nr = nr ORDER BY krita_db.id DESC

I assume table with complaint is krita_db

SQL UPDATE Statement - Updating Data in a Table, are capable of are projection, selection, and joining. Projection refers to the restriction of columns selected from a table. SQL: selecting rows where column value changed from previous row It will return first row and rows where value is different. Selecting rows where column value

You can just use SELECT DISTINCT instead of SELECT. That will remove all your duplicate rows.

What does * mean in sql?, How do I select a record without duplicates in one column in SQL? SELECT CASE status WHEN 'VS' THEN 'validated by subsidiary' WHEN 'NA' THEN 'not acceptable' WHEN 'D' THEN 'delisted' ELSE 'validated' END AS STATUS FROM SUPP_STATUS. This is using the CASE This is another to manipulate the selected value for more that two options. Share a link to this answer. improve this answer. answered Jun 2 '17 at 12:58.

List the Capabilities of SQL SELECT Statements, In other words, the DISTINCT clause treats all NULL “values” as the same value. SQL Server SELECT DISTINCT examples. For the demonstration, we will use the​  I have two tables in my SQL Server database. The first is Test1 and second is Test2. There is a column RegNo in both tables. Now I want to select the values from both the tables for a particular R

SQL DISTINCT: Removing Duplicates In a Result Set Examples, Select rows with same id but different value in another column · sql. I tried for hours and read many posts but I still can't figure out how to handle  SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

SQL Server SELECT DISTINCT, Using standard SQL on most RDBMS, there are various ways. Using a subquery: SELECT d.dept, d.role1, d.role2, DEF FROM data d INNER  In such a case, the SQL expression preceding the operator evaluates to a single value, and the subquery following the operator must also evaluate to a single value. An exception is the case of the quantified comparison operator, which is a comparison operator followed by a quantifier ( ANY , SOME , or ALL ).

Comments
  • EXISTS is the right answer. I would upvote if the first query were removed.
  • @GordonLinoff First query and fiddle were for the original question OP asked. Later OP edited the question and updated the expected output. Anyway, I am removing it as it's not answering anything now.
  • Left join? I don't think that Will help
  • It's will list all complaints if it not match the employee_nr I wrote this base on this expected result cause I did't clearly understand the case he want
  • i have updated the post now for better explaining what i'm trying to do!