I have a table, horribly designed (not my doing thankfully), that stores data in a fashion similar to the following:
[key], [lease_id], [building_name], ~20 more columns of data
A lease_id can and will exist for a centre as well as head office. I've been asked to find all instances where data in a building for a lease doesn't match data in head office for the same lease.
I can do this, quite easily, with a self join. The challenge here is that there are about 20 columns to compare and although I could type each one in manually I was wondering if there's a better way to do this (which would also mean the query can be used in future, accounting for any table changes).
In syntaxtically ridiculous psuedo code- I want to do something similar to what the following would do if it were to work:
select lp.*
from lease_proposal lp
inner join
(
select *
from lease_proposal lp2
where building_id = '001' -- assume 001 is head office for sake of example
) lp2
on lp2.lease_id = lp.lease_id
where lp.* <> lp2.*
The character “*” has special meaning in SQL. Using it will return every column for the table specified. Since there is no WHERE clause specified, every row will be returned as well. The alternative would be to list each column individually: select empno,ename,job,sal,mgr,hiredate,comm,deptno from emp.
We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected.
How do you check if data in one table exists in another table Excel? You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column.
You could do an INTERSECT
operation to find all rows where all data matched, then LEFT JOIN
that result and select only the rows where there wasn't an intersection:
SELECT
a.*
FROM
lease_proposal a
LEFT JOIN
(
SELECT *
FROM lease_proposal
INTERSECT
SELECT *
FROM lease_proposal
WHERE building_id = 001
) b ON a.lease_id = b.lease_id
WHERE
b.lease_id IS NULL
If SQL Server supported it, you could also use a NATURAL LEFT JOIN
like so:
SELECT
a.*
FROM
lease_proposal a
NATURAL LEFT JOIN
(
SELECT *
FROM lease_proposal
WHERE building_id = 001
) b
WHERE b.lease_id IS NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With