April 19, 2011 at 12:24 pm
I have two tables and I want to find the diagonal mismatch between the two table rows I have three field on the bases of this both table are joined
Example of data :-
Table 1Table 2
Cat 1Cat2Cat3Cat4Cat 5Cat 6Cat 7Cat8
ABCDABCD
ABCDABCD
ABCFABCD
ABCFABCE
We are matching the or joined the table on three joins
cat 1 = cat 5
cat 2 = cat 6
cat 3 = cat 7
We want to find the diagonal mismatch on cat 4 and cat8
so our expected output :-
ABCF
ABCE
😛
April 19, 2011 at 1:26 pm
If you want tested help from those who can help you, post your table definition(s), sample data and the desired result from that sample data following the instructions which you can access by clicking on the first link in my signature block.
That said here is part of what you should post.
CREATE TABLE #T1(Cat1 CHAR(1),Cat2 CHAR(1), Cat3 CHAR(1), Cat4 CHAR(1), Cat5 CHAR(1),
Cat6 CHAR(1), Cat7 CHAR(1), Cat8 CHAR(1))
April 20, 2011 at 12:26 am
table definitions are
SELECT 'A' AS COL1,'B' AS COL2,'C' AS COL3,'D' AS COL4 INTO #TABLE1
UNION ALL
SELECT 'A' , 'B','C','D'
UNION ALL
SELECT 'A' , 'B','C','F'
UNION ALL
SELECT 'A' , 'B','C','F'
SELECT 'A' AS COL1,'B' AS COL2,'C' AS COL3,'D' AS COL4 INTO #TABLE2
UNION ALL
SELECT 'A' , 'B','C','D'
UNION ALL
SELECT 'A' , 'B','C','D'
UNION ALL
SELECT 'A' , 'B','C','E'
all columns are varchar type only
TABLE1TABLE2MATCH CRIETRIA
COL1COL2COL3COL5COL6COL7COL4COL8FLAG
ABC ABC FENON-MATCH
the data in table 1 the data in table 1
TABLE 1TABLE 2
COL1COL2COL3COL4COL1COL2COL3COL4
ABCDABCD
ABCDABCD
ABCFABCD
ABCFABCE
AS u can see that from table1 and table 2
in two of the columns (col4 and col8) only value "f" from the table1 and vale "e" from the table2 are not matching but rest i.e. "D" is matching in both tables.
the matching crietria in both the tables is:
and joining criteria is
col1=col4
col2=col5
col3=col6
and expetected output is
E
F
means the output should be like this
A B C E
A B C F
hope its helpful to understand the problem,
if not please ask further clarifications...
April 20, 2011 at 10:37 am
First of all I would place all your data from Table 1 and Table 2 into a single temporary table
CREATE TABLE #T3(COL1 VARCHAR(1), COL2 VARCHAR(1),COL3 VARCHAR(1), COL4 VARCHAR(1)
,COL1A VARCHAR(1),COL2A VARCHAR(1),COL3A VARCHAR(1), COL4A VARCHAR(1))
INSERT INTO #T3
SELECT 'A', 'B','C','D','A','B','C','D' UNION ALL
SELECT 'A', 'B','C','D','A','B','C','D' UNION ALL
SELECT 'A', 'B','C','F','A','B','C','D' UNION ALL
SELECT 'A', 'B','C','F','A','B','C','E'
Then I would execute the following:
SELECT col1,col2,col3,col4,col1A,col2A,col3A,col4A FROM #T3
GROUP BY col1,col2,col3,col4,col1A,col2A,col3A,col4A
HAVING COUNT(*) = 1
Result:
col1col2col3col4col1Acol2Acol3Acol4A
ABCFABCD
ABCFABCE
April 21, 2011 at 7:13 am
hi thanks for replying me within time,
but we are not looking for this result.
but we are looking for out below result as follows
A B C E
A B C F
because
the column a b c d exists in both tables
but F is not exists in the table2 , so we need F as result
and also E which is in table2 and not in the table1. we need E as result.
this means we need those records which are exists in onetable and not exists in second table and viceversa, after applying the joining criteria
col 1 = col 5 means a=a
col 2 = col 6 means b=b
col 3 = col 7 means c=c
so we need resultant data as follows
ABCF -> this combination does not exists in table2 but exists in table1 so we need this record
ABCE -> this combination does not exists in table1 but exists in table 2 so we need this record.
and also thanks for taking your time for helping me.
April 21, 2011 at 7:46 am
-- Correct result, dodgy logic
(SELECT *
FROM #TABLE1
EXCEPT
SELECT * FROM #TABLE2)
UNION ALL
(SELECT *
FROM #TABLE2
EXCEPT
SELECT * FROM #TABLE1)
-- correct result, correct logic
SELECT t1.*
FROM #TABLE1 t1
WHERE EXISTS (
SELECT 1 FROM #TABLE2 t2
WHERE t1.COL1 = t2.COL1
AND t1.COL2 = t2.COL2
AND t1.COL3 = t2.COL3
AND t1.COL4 <> t2.COL4 )
AND NOT EXISTS (
SELECT 1 FROM #TABLE2 t2
WHERE t1.COL1 = t2.COL1
AND t1.COL2 = t2.COL2
AND t1.COL3 = t2.COL3
AND t1.COL4 = t2.COL4 )
UNION
SELECT t2.*
FROM #TABLE2 t2
WHERE EXISTS (
SELECT 1 FROM #TABLE1 t1
WHERE t1.COL1 = t2.COL1
AND t1.COL2 = t2.COL2
AND t1.COL3 = t2.COL3
AND t1.COL4 <> t2.COL4 )
AND NOT EXISTS (
SELECT 1 FROM #TABLE1 t1
WHERE t1.COL1 = t2.COL1
AND t1.COL2 = t2.COL2
AND t1.COL3 = t2.COL3
AND t1.COL4 = t2.COL4 )
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 21, 2011 at 8:06 am
SELECT a.COL1,a.Col2,a.Col3,a.Col4
FROM (
SELECT a.COL1,a.Col2,a.Col3,a.Col4
FROM #TABLE1 a
EXCEPT
SELECT b.COL1,b.Col2,b.Col3,b.Col4
FROM #TABLE2 b
) a
UNION ALL
SELECT b.COL1,b.Col2,b.Col3,b.Col4
FROM (
SELECT b.COL1,b.Col2,b.Col3,b.Col4
FROM #TABLE2 b
EXCEPT
SELECT a.COL1,a.Col2,a.Col3,a.Col4
FROM #TABLE1 a
) b
Far away is close at hand in the images of elsewhere.
Anon.
April 22, 2011 at 3:35 am
hi,
thanks for your help, need some more solutions so that we can get the output which we are looking for.
April 22, 2011 at 2:45 pm
CREATE TABLE #T1 (cat1 char(1), cat2 char(1),cat3 char(1), cat4 char(1))
CREATE TABLE #T2 (cat1 char(1), cat2 char(1),cat3 char(1), cat4 char(1))
INSERT INTO #T1
SELECT 'A', 'B','C','D' UNION ALL
SELECT 'A', 'B','C','D' UNION ALL
SELECT 'A', 'B','C','F' UNION ALL
SELECT 'A', 'B','C','F'
INSERT INTO #T2
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','B','C','E'
;with cte as (
select cat1,cat2,cat3,cat4
from #T1
where exists ( select 1 from #T2
where #T1.cat1 = #T2.cat1
and #T1.cat2 = #T2.cat2
and #T1.cat3 = #T2.cat3
and #T1.cat4 <> #T2.cat4)
and not exists (select 1 from #T2
where #T1.cat1 = #T2.cat1
and #T1.cat2 = #T2.cat2
and #T1.cat3 = #T2.cat3
and #T1.cat4 = #T2.cat4)
union all
select cat1,cat2,cat3,cat4
from #T2
where exists ( select 1 from #T1
where #T1.cat1 = #T2.cat1
and #T1.cat2 = #T2.cat2
and #T1.cat3 = #T2.cat3
and #T1.cat4 <> #T2.cat4)
and not exists (select 1 from #T1
where #T1.cat1 = #T2.cat1
and #T1.cat2 = #T2.cat2
and #T1.cat3 = #T2.cat3
and #T1.cat4 = #T2.cat4)
) -- end of cte
select distinct * from cte
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply