How do I write some SQL which gives the result set for the records that
are returned by one query and not returned by another query?

Here are my 2 queries :

1)select distinct ups.SerialNumber, 
        ups.Division,
        ups.Family,
        ups.Product,
        ups.ContractStart,
        ups.SAPContractNumber,
        ups.ContractType,
        ups.AccountNumber,
        ups.Territory
from UploadDataStaging ups

2) select distinct ups.SerialNumber,
        com.CompanySiteKey,
        ups.Division,
        ups.Family,
        ups.Product,
        ups.ContractStart,
        ups.SAPContractNumber,
        ups.ContractType,
        ups.AccountNumber,
        ups.Territory
from UploadDataStaging ups inner join CompanySite com on
ups.CustomerName = com.[Name]
where com.[name] = ups.CustomerName and com.Address1 = ups.Street1 and
com.Address2 = ups.Street2

BTW, it is the second query that is returning the extra records.



*** Sent via Developersdex http://www.developersdex.com ***

If the second query is returning more records, that is because you have more
than one record with the same ups.CustomerName, ups.Street1, and ups.Street2.

This means you are having records from ups showing up more than once, as it
displays them once for each record in the com table that matches everything
in the on and where clauses.

BTW, you do not need to repeat the logic in the where clause.  In fact you
can write the query completely in the join...
select distinct ups.SerialNumber,
        com.CompanySiteKey,
        ups.Division,
        ups.Family,
        ups.Product,
        ups.ContractStart,
        ups.SAPContractNumber,
        ups.ContractType,
        ups.AccountNumber,
        ups.Territory
from UploadDataStaging ups inner join CompanySite com on
ups.CustomerName = com.[Name]
and com.Address1 = ups.Street1 and
com.Address2 = ups.Street2

if you want to see the com records that are duplicated run the following
query:
Select com.* from CompanySite com
  inner join (Select [name], Address1,Address2
        from companysite
         GROUP BY com.[name], com.Address1,com.Address2
         having count(*) >1) comDup
    on comDup.[name] = com.[name]  and comDup.Address1 = com.Address1  and
compdup.Address2 = com.Address2
order by com.[name], com.Address1,com.Address2, com.CompanySiteKey


Warning:  There may be some minor sytax problems, since I just typed this
off the top of my head.

Ryan




Show quoteHide quote

Thanks for the advice Ryan...although I can't get your query to work,
and it's a bit too complex for me to know how to fix.  Can you help?



*** Sent via Developersdex http://www.developersdex.com ***

OK.  Can you give me the exact error that is returning.

Also to get just the names and addresses of the duplicates, but not the
exact rows:
Select [name], Address1,Address2, count(*)
from companysite
GROUP BY com.[name], com.Address1,com.Address2
having count(*) >1

BTW, the "Complex" query inside the join is called a subquery.  I have
reattached the entire query below with explanation between the comments (/*
*/) of what it is doing, so it may help in debugging it:
/*This part is to return all rows from Companysite */
Select com.*
from CompanySite com
/*The "inner join" returns only those records that the subquery identifies
as having duplicates by making sure that only records that have a matching
name and address are returned */
  inner join
/* open paren says in this case to identify what follows into the subquery */
(
/*return to the main query only those rows that we want*/
Select [name], Address1,Address2
        from companysite
/* Aggregate all of the names and address into a single record */
         GROUP BY com.[name], com.Address1,com.Address2
/* limit those rows returned to only those that have more than 1 record with
the aggregated fields (in this case name, address1, address2) */
         having count(*) >1
/* close paren ends the subquery, while the "compDup" aliases the subquery
so that it is treated like a table in the "Inner Join" */
) comDup
    on comDup.[name] = com.[name]  and comDup.Address1 = com.Address1  and
compdup.Address2 = com.Address2
/* Order by rearranges the output so that it puts all of the records with
the same name and address next to each other */
order by com.[name], com.Address1,com.Address2, com.CompanySiteKey

I hope this helps,
Ryan

Show quoteHide quote

The errors I am getting are :

The column prefix 'com' does not match with a table name or alias name
used in the query.

The column prefix 'compdup' does not match with a table name or alias
name used in the query.



*** Sent via Developersdex http://www.developersdex.com ***

Here are the corrected queries:
-------------------------------------------------------------
Select [name], Address1,Address2, count(*)
from companysite
GROUP BY [name], Address1,Address2
having count(*) >1

---------------------------------------------------------------
/*This part is to return all rows from Companysite */
Select com.*
from CompanySite com
/*The "inner join" returns only those records that the subquery identifies
as having duplicates by making sure that only records that have a matching
name and address are returned */
  inner join
/* open paren says in this case to identify what follows into the subquery */
(
/*return to the main query only those rows that we want*/
Select [name], Address1,Address2
        from companysite
/* Aggregate all of the names and address into a single record */
         GROUP BY [name], Address1, Address2
/* limit those rows returned to only those that have more than 1 record with
the aggregated fields (in this case name, address1, address2) */
         having count(*) >1
/* close paren ends the subquery, while the "compDup" aliases the subquery
so that it is treated like a table in the "Inner Join" */
) comDup
    on comDup.[name] = com.[name]  and comDup.Address1 = com.Address1  and
compdup.Address2 = com.Address2
/* Order by rearranges the output so that it puts all of the records with
the same name and address next to each other */
order by com.[name], com.Address1,com.Address2, com.CompanySiteKey

--------------------------------------------
Ryan

Show quoteHide quote

I have no blog
Trigger logic - this ok
"Order By" Parameter to a Stored Procedure
Database design question
Displaying time as 1:20 PM