Advanced SQL nested EXCEPT

Hi

I want to compare two tables based on two fields that are the same in both. Otherwise tables are not similar, so they have different number of fields that contain different data. I need to get the rows that exist in only one of the tables (in {NavCompanyForValidation table and not in {CompanyComparing}). I am using SQL EXCEPT clause. This part of the code works

SELECT {NavCompaniesForValidation}.[CompanyCode], {NavCompaniesForValidation}.[BusinessIdentityCode] FROM {NavCompaniesForValidation}
EXCEPT
SELECT {CompanyComparing}.[CompanyCode], {CompanyComparing}.[BusinessIdentityCode] FROM {CompanyComparing}

I have a sample data and I can get the rows (only one in this data) correctly. Test output looks like this 


The problem is how to get the actual row in {NavCompaniesForValidation} instead of only those two fields. I tried to implement nested SELECT clause (and changed the Output Entities / Structures to correspond the correct entity) but it gives me either all rows or none depending whether I have NOT in front of EXISTS. 

SELECT {NavCompaniesForValidation}.* FROM {NavCompaniesForValidation}
WHERE NOT EXISTS    
    (
    SELECT {NavCompaniesForValidation}.[CompanyCode], {NavCompaniesForValidation}.[BusinessIdentityCode] FROM {NavCompaniesForValidation}
    EXCEPT
    SELECT {CompanyComparing}.[CompanyCode], {CompanyComparing}.[BusinessIdentityCode] FROM {CompanyComparing}
    )

So how can I get the whole row based on {NavCompaniesForValidation} entity structure?

Hi Jussi,

is there a particular reason why you are using except.  If you would use ´  not exists ´   clause instead, you have no requirement for both selects to have same columns.

So something like

SELECT * 
FROM {NavCompaniesForValidation} V
WHERE NOT EXISTS
(  SELECT * 
   FROM {CompanyComparing} C 
   WHERE C.[CompanyCode] = V.[CompanyCode]
   AND C.[BusinessIdentityCode] = V.[BusinessIdentityCode]
)

Dorine

Hi @Dorine Boudry 

Thank you for your reply. I used EXCEPT because it seemed to be the one I needed. Your code (with minor modifications) did actually do the job. My Advanced SQL didn't understand those aliases for some reason. This code gives now the correct outcome. Thanks!

SELECT {NavCompaniesForValidation}.*
FROM {NavCompaniesForValidation} 
WHERE NOT EXISTS
(  SELECT * 
   FROM {CompanyComparing} 
   WHERE {CompanyComparing}.[CompanyCode] = {NavCompaniesForValidation}.[CompanyCode]
   AND {CompanyComparing}.[BusinessIdentityCode] = {NavCompaniesForValidation}.[BusinessIdentityCode]
)

Yes, I had the feeling that aliases might not be exactly right, but i was answering from my phone and didn´ t feel like retyping the whole entity names in the where clause ;-)

Dorine

 

Hello @Jussi Ahonen,

Did I understand your scenario correctly below?


Table: NavCompaniesForValidation

Table: CompanyComparing


Advanced Query Result: All columns from Table NavCompaniesForValidation


If I understood your ask correctly, then it is a case of an INNER JOIN and you can get the results with:

EITHER an advanced query as shown below:

SELECT * FROM {NavCompaniesForValidation}
INNER JOIN {CompanyComparing}
    ON {NavCompaniesForValidation}.[CompanyCode] = {CompanyComparing}.[CompanyCode]
    AND {NavCompaniesForValidation}.[BusinessIdentityCode] = {CompanyComparing}.[BusinessIdentityCode]

OR by using an aggregate as shown below:

If I got it all wrong, please use the table data in the above tables to help understand what the final result should look like. Hope this helps,

Regards,

AJ

Hi @=AJ= 

You got it wrong :)

I need (from above tables) the first and the fifth row from NavCompaniesForValidation table because no NavCompaniesForValidation.CompanyCode neither NavCompaniesForValidation.BusinessIdentityCode have a match in CompanyComparing table. 

Jussi

Hi Jussi,

is there a particular reason why you are using except.  If you would use ´  not exists ´   clause instead, you have no requirement for both selects to have same columns.

So something like

SELECT * 
FROM {NavCompaniesForValidation} V
WHERE NOT EXISTS
(  SELECT * 
   FROM {CompanyComparing} C 
   WHERE C.[CompanyCode] = V.[CompanyCode]
   AND C.[BusinessIdentityCode] = V.[BusinessIdentityCode]
)

Dorine

Hi @Dorine Boudry 

Thank you for your reply. I used EXCEPT because it seemed to be the one I needed. Your code (with minor modifications) did actually do the job. My Advanced SQL didn't understand those aliases for some reason. This code gives now the correct outcome. Thanks!

SELECT {NavCompaniesForValidation}.*
FROM {NavCompaniesForValidation} 
WHERE NOT EXISTS
(  SELECT * 
   FROM {CompanyComparing} 
   WHERE {CompanyComparing}.[CompanyCode] = {NavCompaniesForValidation}.[CompanyCode]
   AND {CompanyComparing}.[BusinessIdentityCode] = {NavCompaniesForValidation}.[BusinessIdentityCode]
)

Yes, I had the feeling that aliases might not be exactly right, but i was answering from my phone and didn´ t feel like retyping the whole entity names in the where clause ;-)

Dorine

 

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.