Geek asks and answers

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

I have a terribly designed table (not my thanks) that stores data in a style similar to the following:

[key], [lease_id], [event_name], 20 more data columns

Rent_id can and will exist for both the center and the head office. I was asked to find all the copies in which the data in the building for rent does not match the data in the head office for the same rent.

I can do this quite easily with self-join. The problem here is that there are about 20 columns for comparison, and although I can enter them manually, I was wondering if there is a better way to do this (which also means that the query can be used in the future, given any changes to the table).

In the psuedo syntactically ridiculous code - I want to do something similar to what the following will do if this works:

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.*
+5
source share
1 answer

You can perform an operation INTERSECTto find all rows where all the data coincided, and then LEFT JOINthat will display and select only rows in which there was no 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 supports it, you can also use NATURAL LEFT JOINthis:

SELECT  
    a.*
FROM
    lease_proposal a
NATURAL LEFT JOIN
    (
        SELECT *
        FROM lease_proposal
        WHERE building_id = 001
    ) b
WHERE b.lease_id IS NULL
+5
source

More articles:


All Articles