How to get relating columns from an Except clause

Question

1.00/5 (1 vote)

See more:

Hi,
I have two table to compare a locate the differences. I want to know which records are in tblExample2 and not in tblExample1 based on the column ProjectNr. And if there are differences, show me the remaining columns relating to detected ProjectNr. Is that possible?

tblExample1

Year	BookingNr	ProjectNr	ProjectName	Area	Location	Finance	Price A	Price B	Plast	Pnow	Plan 1	Plan 2	Plan 3	Plan 4
01.2011	16	TT.0161110	Acc-Gha	E	Ost	B	2.516,00	2.516,00	2.516,00					
01.2011	16	TT.0161110	Acc-Gha	E	Ost	LU	1.199.337,78	931.337,78	78.337,80	55.000,00	150.000,00	134.000,00	206.000,00	150.000,00
01.2011	16	TT.0161110	Acc-Gha	E	Ost	A	513.000,00	513.000,00	blank	blank	50.000,00	70.000,00	100.000,00	120.000,00
01.2011	16	TT.0161110	Acc-Gha	E	Ost	LU	5.135.000,00	4.976.000,00	blank	blank	250.000,00	750.000,00	1.100.000,00	1.400.000,00
01.2011	16	T.016064	Nrg-Lag	E	Südost	LU	blank	735.000,00	blank	blank	blank	blank	blank	
01.2011	16	T.016064	Nrg-Lag	E	Südost	LU	blank	4.161.000,00	blank	blank	blank	blank	blank	
01.2011	31	PP.031001	Bln-Port	E	Süd	A	2.967,00	2.967,00	2.967,00	blank	blank	blank	blank	
01.2011	31	PP.031001	Bln-Port	E	Süd	LA	33.889,68	31.630,18	12.630,20	2.740,50	4.000,00	8.000,00	1.000,00	
01.2011	31	PP.031001	Bln-Port	E	Süd	LA	70.000,00	70.000,00	blank	blank	35.000,00	35.000,00	blank	
01.2011	11	G.011210	Alt-Nyc	E	Südost	LS	blank	4.000,00	blank	blank			blank	
01.2011	11	G.011210	Alt-Nyc	E	Südost	LS	blank	80.000,00	blank	blank			blank	
01.2011	16	X.016270181506	Fraport-Schil	D	Ost	FE	46.702,71	50.335,23	50.335,20	3.632,52			0	
01.2011	16	X.016270181506	Fraport-Schil	D	Ost	V3	430.122,03	430.122,03	430.122,00					
01.2011	16	X.016270181506	Fraport-Schil	D	Ost	W	341.374,00	341.374,00	341.374,00					
01.2011	16	X.016270181506	Fraport-Schil	D	Ost	FE	173.415,55	173.415,55	169.415,40				0	
01.2011	16	X.016270181506	Fraport-Schil	D	Ost	V3	3.969.787,11	3.969.787,11	3.969.787,10					
01.2011	16	XX.01681514600	Pot-Ess	D	Ost	V3	175.649,82	175.649,82	175.649,80					
01.2011	16	XX.01681514600	Pot-Ess	D	Ost	V4	20.627,25	20.627,25	20.627,30

tblExample 2

Year	BookingNr	ProjectNr	ProjectName	Area	Location	Finance	Price A	Price B	Plast	Pnow	Plan 1	Plan 2	Plan 3	Plan 4
02.2011	16	TT.0161110	Acc-Gha	D	Ost	B	2.516,00	2.516,00	2.516,00					
02.2011	16	TT.0161110	Acc-Gha	D	Ost	LU	1.199.337,78	960.337,78	78.337,80	55.000,00	150.000,00	134.000,00	206.000,00	150.000,00
02.2011	16	TT.0161110	Acc-Gha	D	Ost	A	513.000,00	513.000,00	blank	blank	50.000,00	70.000,00	100.000,00	120.000,00
02.2011	16	TT.0161110	Acc-Gha	D	Ost	LU	5.135.000,00	4.976.000,00	blank	blank	250.000,00	750.000,00	1.100.000,00	1.400.000,00
02.2011	16	T.016064	Nrg-Lag	D	Südost	LU	blank	735.000,00	blank	blank	blank	blank	blank	
02.2011	16	T.016064	Nrg-Lag	D	Südost	LU	blank	4.161.000,00	blank	blank	blank	blank	blank	
02.2011	31	PP.031001	Bln-Port	D	Süd	A	2.967,00	2.967,00	2.967,00	blank	blank	blank	blank	
02.2011	31	PP.031001	Bln-Port	D	Süd	LA	33.889,68	33.630,18	12.630,20	2.740,50	4.000,00	8.000,00	1.000,00	
02.2011	31	PP.031001	Bln-Port	D	Süd	LA	70.000,00	70.000,00	blank	blank	35.000,00	35.000,00		
02.2011	11	G.011210	Alt-Nyc	D	Südost	LS	blank	4.000,00	blank	blank				
02.2011	11	G.011210	Alt-Nyc	D	Südost	LS	blank	80.000,00	blank	blank				
02.2011	16	X.016270181506	Fraport-Schil	D	Ost	AZ	blank	57.000,00						
02.2011	16	X.016270181506	Fraport-Schil	D	Ost	FE	46.702,71	50.335,23	50.335,20	3.632,52			0	
02.2011	16	X.016270181506	Fraport-Schil	D	Ost	V3	430.122,03	430.122,03	430.122,00					
02.2011	16	X.016270181506	Fraport-Schil	D	Ost	W	341.374,00	341.374,00	341.374,00					
02.2011	16	X.016270181506	Fraport-Schil	D	Ost	AZ		590.000,00						
02.2011	16	X.016270181506	Fraport-Schil	D	Ost	FE	173.415,55	173.415,55	169.415,40				0	
02.2011	16	XX.01681514600	Pot-Ess	D	Ost	V	blank	blank	blank		0			0
02.2011	16	XX.01681514600	Pot-Ess	D	Ost	V3	175.649,82	175.649,82	175.649,80					
02.2011	16	XX.01681514600	Pot-Ess	D	Ost	V4	20.627,25	20.627,25	20.627,30

The first possible answer has the a sp like this:

SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr] 
 FROM ' + @Table_Name + ' WHERE [Area] = ''D''' + 
 ' EXCEPT
 SELECT [ProjectNr], [ProjectName], [BookingNr]
 FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
 ' ORDER BY [ProjectNr] DESC '

Possible Answer 1

ProjectNr	ProjectName	BookingNr
TT.0161110	Acc-Gha	16
T.016064	Nrg-Lag	16
PP.031001	Bln-Port	31
G.011210	Alt-Nyc	11

And the answer is based on this statement:

SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM  (
	SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

	FROM ' + @Table_Name + ' WHERE [Area] = ''D''' + 
	' EXCEPT
	SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
 
	FROM  ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
') AS T ' +
' GROUP BY [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

Possible Answer 2

BookingNr	ProjectNr	ProjectName	Finance	Price A
16	TT.0161110	Acc-Gha	B	2.516,00
16	TT.0161110	Acc-Gha	LU	1.199.337,78
16	TT.0161110	Acc-Gha	A	513.000,00
16	TT.0161110	Acc-Gha	LU	5.135.000,00
16	T.016064	Nrg-Lag	LU	blank
16	T.016064	Nrg-Lag	LU	blank
31	PP.031001	Bln-Port	A	2.967,00
31	PP.031001	Bln-Port	LA	33.889,68
31	PP.031001	Bln-Port	LA	70.000,00
11	G.011210	Alt-Nyc	LS	blank
11	G.011210	Alt-Nyc	LS	blank
16	X.016270181506	Fraport-Schil	AZ	blank
16	XX.01681514600	Pot-Ess	V	blank

Exported records / solution should look like this:

Possible Answer 3

BookingNr	ProjectNr	ProjectName	Finance	Price A
	16	TT.0161110	Acc-Gha	B	2.516,00
	16	TT.0161110	Acc-Gha	LU	1.199.337,78
	16	TT.0161110	Acc-Gha	A	513.000,00
	16	TT.0161110	Acc-Gha	LU	5.135.000,00
	16	T.016064	Nrg-Lag	LU	blank
	16	T.016064	Nrg-Lag	LU	blank
	31	PP.031001	Bln-Port	A	2.967,00
	31	PP.031001	Bln-Port	LA	33.889,68
	31	PP.031001	Bln-Port	LA	70.000,00
	11	G.011210	Alt-Nyc	LS	blank
	11	G.011210	Alt-Nyc	LS	blank

My Main Question is or the records which am really interested in should look like the first possible answer without the X’s But with the other columns relating to it (like possible answer 3). Is that possible although the finance column to the X's are new in table Example2 .I have updated my main from yesterday

Posted 5-Nov-14 5:49am

mikybrain1

Updated 6-Nov-14 4:49am

Maciej Los

v6

Add a Solution

Comments

PIEBALDconsult 5-Nov-14 12:13pm

I'd use a LEFT OUTER JOIN and test for IS NULL.

mikybrain1 5-Nov-14 13:00pm

Yes i wanna filter by Area. Let me try to explain to u again.
I want to first have the difference of the two tables based on the ProjectNr WHERE Area = 'D' and if there is a difference (which there is), it should also give me the coresponding columns records related to the differences found.

I hope u understand me a lil' bit now. Thanks

PhilLenoir 5-Nov-14 12:28pm

I'm having a few problems understanding your exact requirement as none of your code (filtered on Area D), your explanation or your example exactly match.

I get that you want a row set that only shows the differing records including their details but:

Do you only want the differing column values shown (blank where there is no difference) or all of the columns for each differing row?
Do you want to filter by Area D?

These both affect the complexity of the solution.

Tomas Takac 5-Nov-14 13:49pm

I doubt that your query returns the results you claim it is. Only last three records from tblExampl1 have Area = 'D' while all records from tblExample2 satisfy the condition. Former EXCEPT latter would return 0 rows. Last but not least, what's the key in your tables? I see ProjectNr is not unique. So how do you want to match the rows to check if they have changed?

PhilLenoir 5-Nov-14 15:13pm

Let me try to explain again what the challenges are:

Your selection "key" appears to be a compound of ProjectNr and ProjectName. However, these are not keys on either table (there are multiple rows having the same values for these fields with differing attributes). What are the Primary Keys on these two tables? If the two tables are "heaps" (no Primary Key), I don't see how you can do a row by row comparison; you will get a Cartesian product across any attempt to relate the tables (e.g. 4 rows for AD.300 Ber-Port or 4 ways I can compare these rows, 1A to 1A, 1A to 2B, 1B to 2A and 1B to 2B). If this is correct, unless the rows are duplicates (and presumably redundant), there will always be differences.

Next, the code fragment you display does not give the result you've shown for the table examples you gave.

Finally, It may be me being dense, but your explanation doesn't give a concrete mathematical description of the result you want. This might be OK if you illustrated with an example that makes sense, but I can't easily see how you get from your tables to your "expected" results.

My suggestions are:
Explain your schema (what are the keys).
Explain what information you want output from EACH table subject to which conditions.
Give example inputs and outputs that you have checked carefully.

One last comment: Reply to the person asking the question. If you don't do that, the person receives no notification.

Good luck!

Tomas Takac 5-Nov-14 17:55pm

I'd say this almost counts as an answer.

PhilLenoir 6-Nov-14 8:55am

:)

mikybrain1 6-Nov-14 4:42am

Good Morning,
So let us try again.Have done my home work but i still dont get it. First of i have two tables and the results after the differences depending on the sql sp.There are no primary keys but the two tables are just very identical.
The first possible answer has the a sp like this:
SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
' ORDER BY [ProjectNr] DESC '

And the 2. answer is based on this statement:

SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM (
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
') AS T ' +
' GROUP BY [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

ORDER BY [ProjectNr] DESC '

My Main Question is or the records which am really interested in sholud look like the first possible answer without the X's BuT with the other columns relating to it (like possible answer 3). Is that possible although the finance column to the X's are new in tbl Example2

I have updated my main from yesterday :)
Thanks for ur help

PhilLenoir 6-Nov-14 10:15am

I appreciate that language is a barrier here, but you are still not clear. Let's look a one record, in your answer 1 we have:
TT.0161110 Acc-Gha 16
Why should this record be present as all TT.0161110 records appear to be present and the same in both tables?

There must be some (simple?) logic at play here. Let's try to deconstruct:
"Show me all records in table 2 that are not present in table 1" is this correct? if this is not correct then what is the logic of the row set to be produced?
Now, how do we tell if a record is present in table 2 that is in table 1:
ProjectNr and ProjectName appear to be redundant, let's assume that's true, so ProjectNr is one of our "selectors". Booking Nr also appears to be redundant as the BookingNr is the same whenever the ProjectNr is the same? Finance looks a little more promising although there are duplicates for a given ProjectNr (e.g. LU). If we include Price A we seem to be unique, so our "candidate key" (= record selector) appears to be ProjectNr, Finance and Price A, meaning that any records in table 2 that have a combination of values in these fields that are not present in table 1 should be in our result set. Is this true or do more fields need to be thrown into the mix? Are the assumptions correct about redundancy (do they apply to all your data?)
Finally what attributes do we need displayed in our result set.

If you do change your question, please use the formatting tools to make it easier for us to read.

mikybrain1 6-Nov-14 10:44am

hahaha i guess the language is really a barrier. U'r right the tables looks almost the same but the column Area isn't the same that's why am filltering by using the WHERE clause. The first answer is 100% correct.
If u cross check u will notice that for eg. TT.0161110 is in both tables but have a different Area = 'D' in tbl2( So that shd be a new record in tbl2 right?)
I just want to know if there is a trick to get the remaining colums to my answer 1 without having/getting the XX.recordsto it since they also have a different values at Finance (FE & V ) in tbl2.
In tbl1 there are 5 and 2 records to XX...506 and XX...600 respectively BUT in tbl2 there are 6 and 3 records.
My main focus is Answer 1 and its remaining columns. Is it possible? I have tried all i can but no success. Maybe it isn't possible :)

mikybrain1 6-Nov-14 14:21pm

Thnx very much Phil for your support and your patient. I really appreciate it. Am gonna check your solution tomorrow and give you a feed back.

Thnx

PhilLenoir 6-Nov-14 14:35pm

No problem. If you are still stuck, see if you can make out the sense of the "selector key" questions I posed and feel free to ask for more help.

Nelek 6-Nov-14 12:09pm

If you get asked for it and you do it, you should post your code into the question using the widget "improve question" and then the needed code language. It will increase the readability and the easier people can read your code, the more probability for you to get help

Tomas Takac 6-Nov-14 8:29am

It appears you do tblExample2 EXCEPT tblExample1, is that correct? You should post the actual query, not the one which builds your dynamic sql. Furthermore it seems that your key to match records is BookingNr ProjectNr, Location, Finance - correct? You know it would be so much easier if you would create a fiddle[^], and it would help you too.

mikybrain1 6-Nov-14 8:56am

Yes. It should look in tblExample2 and tell me what's new there and not in tblExample1.
The actual queries will be like below respectively:

SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM tblExample2 WHERE [Area] = 'D'
EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM tblExample1 WHERE [Area] = 'D'
ORDER BY [ProjectNr] DESC

SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM (
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM tblExample2 WHERE [Area] = 'D'
EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM tblExample1 WHERE [Area] = 'D'
) AS T
GROUP BY [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
ORDER BY [ProjectNr] DESC

That will be great from u if u can help me accomplish my intention. My intention is to get the records like in possible Answer 3

Thnx

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2014-11-06T04:53:00

After all the discussions we had I think this is what you need:

SQL

SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM tblExample2 t2 WHERE [Area] = 'D' 
AND NOT EXISTS
(
  SELECT * FROM tblExample1 t1 WHERE [Area] = 'D'
  AND t2.[ProjectNr] = t1.[ProjectNr] 
  AND t2.[ProjectName] = t1.[ProjectName] 
  AND t2.[BookingNr] = t1.[BookingNr] 
  AND t2.[Finance] = t1.[Finance] 
)
ORDER BY [ProjectNr] DESC

PhilLenoir · Accepted Answer · 2014-11-06T05:12:00

OK, you still didn't actually answer all the questions I asked, but I'm hoping that I have the sense of your requirement. You can achieve the result you want by joining your row set back to the detail table:

SQL

SELECT 
    DISTINCT table2.* 
FROM (
    SELECT ProjectNr, ProjectName, BookingNr
    FROM table2 
    WHERE Area = 'D'
    EXCEPT
    SELECT ProjectNr, ProjectName, BookingNr
    FROM table1 
    WHERE Area = 'D') AS MyRecs 
    INNER JOIN table2 ON MyRecs.ProjectNr = table2.ProjectNr
        AND MyRecs.ProjectName = table2.ProjectName
        AND MyRecs.BookingNr = table2.BookingNr
ORDER BY ProjectNr DESC

Notes:
I still have doubts about your selection key; in your sample data ProjectNr always yields the same ProjectName and BookingNr. I've used DISTINCT to remove the Cartesian product your sample data would yield. You will need to adjust the selection key fields if this is incorrect and if you get unique matches across the inner join you won't need "distinct".
The data looks poorly modelled, but you may have no control on this. If ProjectNr always yields the same ProjectName and BookingNr then these should be moved to related table(s?).
Data without a candidate key is rare. Understanding your candidate key(s) is fundamental to database design. A key can be multiple fields (and in the real world, often is) and is core to the purpose of the data and what it is describing. Sticking an Identity field in is not good enough; sure it will give you an efficient row identifier, but it doesn't explain what the data is about.
You could probably use an inner join between table1 and table2 with appropriate join conditions (note this does not have to be "equals") and/or WHERE conditions to do the same job, but without a better understanding of your data/schema I can't be certain!

How to get relating columns from an Except clause

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0