Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

where table.* <> table.* - Is there a way to do something like this?

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.*
like image 878
Michael A Avatar asked Jul 05 '12 04:07

Michael A


People also ask

What does select * from table return?

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.

How do you get data from a table which is not present in another table?

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.

How do you create a table from another table in SQL?

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 know if data exists in one table and not in another?

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.


1 Answers

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
like image 167
Zane Bien Avatar answered Oct 12 '22 09:10

Zane Bien