How do I compare two columns in the same table?
SELECT * FROM FOO WHERE C1 = C4
should work. Does it not?
If not, are they the same data type and length? You may need to convert.
I don't know about WebSql, but I've seen some db systems that refuse to match if one is a varchar(5) and the other is a varchar(10) even though they hold the same value.
In those systems you have to use something like
Convert(varchar, 10, FieldName)
to get a match.
Comparing values in two columns in same table and creating new column
This is a little tricky because when there is only one row, then you want to keep the original ordering. One method is aggregation with union all
:
select least(sender, receiver) as sender, greatest(sender, receiver) as receiver, 1 as exchanged
from t
group by least(sender, receiver), greatest(sender, receiver)
having count(*) = 2
union all
select sender, receiver, 0
from t
where not exists (select 1
from t t2
where t2.receiver = t.sender and t2.sender = t2.receiver
);
SQL query to compare multiple columns in same table in oracle
You can use MINUS.
if no_data then both are the same, if there are some records - mean that there is a difference between
create table emp as select * from hr.employees;
insert into emp select employee_id+1000, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id,
decode(department_id ,30,70, department_id)
from hr.employees;
select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
from emp where employee_id <= 1000
minus
select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
from emp where employee_id > 1000;
But you have to list all columns, because if you have eg different dates or ids - they will be compared too. But it's easier to list columns in SELECT clause then write for everyone WHERE condition.
Maybe it will help.
-- or if different tables and want to compare all cols simply do
drop table emp;
create table emp as select * from hr.employees;
create table emp2 as
select employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id,
decode(department_id ,30,70, department_id) department_id
from hr.employees;
select * from emp
minus
select * from emp2;
---- ADD DATE CRITERIA
-- yes, you can add date criteria and using analytical functions check which
-- is newer and which is
older and then compare one to another. like below:
drop table emp;
create table emp as select * from hr.employees;
insert into emp
select
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date+1,
job_id,
salary,
commission_pct,
manager_id,
decode(department_id ,30,70, department_id)
from hr.employees;
with data as --- thanks to WITH you retrieve data only once
(select employee_id, first_name, last_name, email, phone_number,
hire_date,
row_number() over(partition by employee_id order by hire_date desc) rn -- distinguish newer and older record,
job_id, salary, commission_pct, manager_id, department_id
from emp)
select employee_id, first_name, last_name, email, phone_number, department_id from data where rn = 1
MIUNUS--- find the differences
select employee_id, first_name, last_name, email, phone_number, department_id from data where rn = 2;
How to compare two columns in the same table, and get a distinct one
You can first fetch the default key,values pairs, excluding the ones that have a value for the corresponindg user and then use UNION
and fetch the key,values pairs for the user.
A query using NOT IN
and a subquery:
SELECT * FROM `options` WHERE `user` IS NULL
AND `key` NOT IN
(SELECT `key` FROM `options` WHERE `user`='John')
UNION ALL
SELECT * FROM `options` WHERE `user`='John'
The same query using JOIN
SELECT o.* FROM `options` o
LEFT JOIN
(SELECT `key` FROM `options` WHERE `user`='John') o2
ON o.key=o2.key WHERE o2.key IS NULL AND o.user IS NULL
UNION ALL
SELECT * FROM `options` WHERE `user`='John'
How do we get the data by comparing two columns of the same table in blade page
According to laravel docs:
The whereColumn method may be used to verify that two columns are equal:
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();
To solve your issue:
$dam = DB::table('performance')->whereColumn('user_id', 'assigned_by')->count();
or
$dam = \App\Performance::whereColumn('user_id', 'assigned_by')->count();
How to compare two row in same table and return the data in response using stored procedure
You need to unpivot all the columns, then join each row to every other.
You can either pivot everything manually using CROSS APPLY (VALUES
SELECT
aId = a.id,
bId = b.id,
v.columnName,
v.value1,
v.value2
FROM @t a
JOIN @t b
ON a.id < b.id
-- alternatively
-- ON a.id = 1 AND b.id = 2
CROSS APPLY (VALUES
('col1', CAST(a.col1 AS nvarchar(100)), CAST(b.col1 AS nvarchar(100))),
('col2', CAST(a.col2 AS nvarchar(100)), CAST(b.col2 AS nvarchar(100))),
('col3', CAST(a.col3 AS nvarchar(100)), CAST(b.col3 AS nvarchar(100))),
('col4', CAST(a.col4 AS nvarchar(100)), CAST(b.col4 AS nvarchar(100)))
) v (ColumnName, Value1, Value2)
WHERE EXISTS (SELECT v.Value1 EXCEPT SELECT v.Value2)
FOR JSON PATH;
The use of WHERE EXISTS (SELECT a.Value1 INTERSECT SELECT a.Value2)
means that nulls will get taken into account properly.
Or you can use SELECT t.* FOR JSON
and unpivot using OPENJSON
WITH allValues AS (
SELECT
t.id,
j2.[key],
j2.value,
j2.type
FROM @t t
CROSS APPLY (
SELECT t.*
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
) j1(json)
CROSS APPLY OPENJSON(j1.json) j2
WHERE j2.[key] <> 'id'
)
SELECT
aId = a.id,
bId = b.id,
columnName = a.[key],
value1 = a.value,
value2 = b.value
FROM allValues a
JOIN allValues b ON a.[key] = b.[key]
AND a.id < b.id
-- alternatively
-- AND a.id = 1 AND b.id = 2
WHERE a.type <> b.type
OR a.value <> b.value
FOR JSON PATH;
db<>fiddle
SQL Fiddle of actual data
Comparing same columns in the same table
Seems a self join would work...
SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN table2 T2
on T1.Column1 = T2.Column1
and T2.column2 = T2.Column2
and T1.Date <> T2.Date
or an exists (faster but access to only T1 data)
SELECT T1.*
FROM Table1 T1
WHERE Exists (Select 1
from table1 T2
where T1.Column1 = T2.Column1
and T2.column2 = T2.Column2
and T1.Date <> T2.Date)
SQL Comparing Two Columns in same table and update in another table
As I understand you have something like linked lists and for each linked list you want to get min and max id.
If you are using SQL Server this should work straight away, otherwise I think you will be able to apply the same method to other rdbms.
Table declaration and test values insert:
CREATE TABLE #t
(
Col1 int,
Col2 int
)
INSERT INTO #t VALUES (100, 200), (200, 300 ), (300, 400), (400, 500), (700, 900)
And the actual code:
SELECT MIN(Col1) AS Col, MAX(Col2) AS New FROM
(
SELECT SUM(SeqIDStart) OVER (ORDER BY Col1 ASC) AS SeqID, *
FROM (
SELECT
CASE WHEN LAG(B.Col1, 0, NULL) OVER (ORDER BY a.Col1 ASC) IS NULL THEN 1 ELSE 0 END AS SeqIDStart,
a.col1 AS Col1, a.Col2 AS Col2, B.Col1 AS adjsCol
FROM
#t a
LEFT JOIN
#t b
ON a.col1 = b.col2
WHERE a.Col1 IS NOT NULL
) a
) b
GROUP BY SeqID
I hope this will help
How to compare two columns in the same table
You will have to join the table with itself and compare. So something like
SELECT *
FROM table as t1
JOIN table as t2
ON t1.requestid = t2.useriD and t1.userid = t2.requestid
Related Topics
Returning Multiple Rows from Querying Xml Column in SQL Server 2008
How to Enable Integration Services (Ssis) in SQL Server 2008
SQL Group by Day, Show Orders for Each Day
Which SQL Command How to Use to See the Structure of a Table on SQL Server
Filter Duplicate Rows Based on a Field
Sync Between SQL Server and MySQL Server
Row_Number() Over Not Fast Enough with Large Result Set, Any Good Solution
Logging Erroneous Queries Only on SQL Server
How to Retrieve Same Column Twice with Different Conditions in Same Table
How to Use SQL Wildcards in Linq to Entity Framework
Help with Writing a SQL Query for Nested Sets
Left Inner Join VS. Left Outer Join - Why Does the Outer Take Longer
T:Sql: Select Values from Rows as Columns
Using with Nolock Table Hint in Query Using View - Does It Propagate Within the View
How to Replace Null Values with a Text
How to Get the Max Row Number Per Group/Partition in SQL Server