CODEWITHSUNDEEP
sql-server
user1030181
user1030181

Reputation: 2015

What are other ways to get this result set

What are other ways to get the same result set

I am using 4 tables

SELECT * FROM Terminal
SELECT * FROM Customer
SELECT * FROM Contract
SELECT * FROM ExternalTable

Data in these 4 tables are as below:

enter image description here

The query that i've written is

    select ex.TerminalName,ex.CustomerName from ExternalTable ex
except
select t.TerminalName,ct.CustomerName from [Contract] c
inner join Terminal t on t.TerminalID=c.TerminalID
inner join Customer ct on ct.CustomerId=c.CustomerId

Which gets the below result

enter image description here

So just curious to know what are the other ways to get this same result

Upvotes: 0

Views: 60

Answers (3)

JosephStyons
JosephStyons

Reputation: 58785

Here is a self-contained example that shows two alternatives. One uses NOT IN, and the other uses a LEFT OUTER JOIN, but they should be equivalent.

--set up temp tables with dummy data to replicate the issue

declare @Terminal table(terminalid int,terminalname nvarchar(100));
insert into @Terminal select 1,'Terminal1' union select 2,'Terminal2'

declare @Customer table(customerid int,customername nvarchar(100));
insert into @Customer select 1,'Customer1' union select 2,'Customer2';

declare @Contract table(contractid int,terminalid int,customerid int,contractname nvarchar(100));
insert into @Contract select 1,1,1,'Contract1';

declare @ExternalTable table(externalid int,terminalname nvarchar(100),customername nvarchar(100),contractname nvarchar(100));
insert into @ExternalTable select 1,'Terminal1','Customer1','Contract1' union select 2,'Terminal2','Customer1','Contract1'

--SELECT * FROM @Terminal
--SELECT * FROM @Customer
--SELECT * FROM @Contract
--SELECT * FROM @ExternalTable

--goal: show records that are in the external table, but are not fully linked in the other tables

--original
select ex.TerminalName,ex.CustomerName from @ExternalTable ex
except
select t.TerminalName,ct.CustomerName from @Contract c
inner join @Terminal t on t.TerminalID=c.TerminalID
inner join @Customer ct on ct.CustomerId=c.CustomerId

--revised (left outer join method)
select et.TerminalName,et.CustomerName
from
  @ExternalTable et
  left join (
    select ex.externalid
    from
      @Contract c
      inner join @Terminal t on t.TerminalID=c.TerminalID
      inner join @Customer ct on ct.CustomerId=c.CustomerId
      inner join @ExternalTable ex on ex.terminalname = t.terminalname and ex.customername = ct.customername
  ) excludes on excludes.externalid = et.externalid
where excludes.externalid is null

--revised ("not in" method)
select et.TerminalName,et.CustomerName
from
  @ExternalTable et
where et.externalid not in(
  select ex.externalid
  from
    @Contract c
    inner join @Terminal t on t.TerminalID=c.TerminalID
    inner join @Customer ct on ct.CustomerId=c.CustomerId
    inner join @ExternalTable ex on ex.terminalname = t.terminalname and ex.customername = ct.customername
  )

Upvotes: 1

shawnt00
shawnt00

Reputation: 17935

This ought to be equivalent:

select ex.TerminalName,ex.CustomerName
from
    ExternalTable ex
    left outer join
    (
        [Contract] c
        inner join Terminal t
            on t.TerminalID=c.TerminalID
        inner join Customer ct
            on ct.CustomerId=c.CustomerId
    )
        on ex.TerminalName = t.TerminalName and ex.CustomerName = ct.CustomerName
where t.TerminalName is null and ct.CustomerName is null

You should note that except returns distinct results and to be strictly equivalent I would need to specify select distinct.

Upvotes: 1

John
John

Reputation: 384

I'm not really what you are looking for, but here is another way to write the query that should produce the same results:

SELECT 
    ex.TerminalName,
    ex.CustomerName 
FROM 
    ExternalTable ex
WHERE
    NOT EXISTS( SELECT
                    NULL
                FROM        
                    [Contract] c
                INNER JOIN 
                    Terminal t on t.TerminalID = c.TerminalID
                INNER JOIN 
                    Customer ct on ct.CustomerId = c.CustomerId
                WHERE
                    t.TerminalName = ex.TerminalName
                AND
                    ex.CustomerName = ct.CustomerName
                )

Upvotes: 1

Related Questions