November 12, 2011 at 2:37 am
Hi Folk,
I'm new to DB.
query which i need is:
I have to retrieve the unmatching record from the two tables, two tables which has 3 same column.
for example
Table A
AaAbAc
1a1001
2b1002
3c1003
4d1006
5d1009
Table B
AaAbAc
1a1001
2b1002
3c1003
4d1007
5d1008
Note: Aa,Ab,Ac is column name in both the table.
Pls reply ASP. Very urgent.
Thanks in Advance
Regards,
Faroz
November 12, 2011 at 4:01 am
Here is something to start with.
SELECT A.*, B.*
FROM A FULL OUTER JOIN B
ON A.ID = B.ID
WHERE A.ID IS NULL
OR B.ID IS NULL
Or MERGE with OUTPUT clause.
[ WITH <common_table_expression> [,...n] ]
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ <output_clause> ] [ OPTION ( <query_hint> [ ,...n ] ) ]
;
November 14, 2011 at 8:10 am
Hi Dev,
Thanks for reply this query
Select A.*,B.*
from A Full Outer Join B
on A.Id=B.id
where A.Id is null
or b.id is null
My tables doesn't have column call "id" then how it will execute.
Pls advice,
November 14, 2011 at 8:39 am
select * from a where a.Aa not in(select Aa from b)
union
select * from b where b.Aa not in(select Aa from a)
November 14, 2011 at 10:04 am
You can use the EXCEPT operator like this:
(SELECT * FROM A
EXCEPT
SELECT * FROM B)
UNION
(SELECT * FROM B
EXCEPT
SELECT * FROM A)
November 15, 2011 at 1:56 am
faroz2003 (11/14/2011)
Hi Dev,Thanks for reply this query
Select A.*,B.*
from A Full Outer Join B
on A.Id=B.id
where A.Id is null
or b.id is null
My tables doesn't have column call "id" then how it will execute.
Pls advice,
I didn't give you a ready-to-use code (spoon feed). I gave you a template & you need to modify it to suit your requirements.
November 15, 2011 at 5:14 am
I believe Joseph Hampton's solution(with a minor modification i.e. UNION ALL instead of UNION) is a much easier solution for SQL 2005+.
(SELECT * FROM A
EXCEPT
SELECT * FROM B)
UNION ALL
(SELECT * FROM B
EXCEPT
SELECT * FROM A)
This way you can easily compare all the fields between the two tables. No need to make a join on every field and then specify IS NULL filter for all columns as was the case with FULL OUTER JOIN solution.
November 15, 2011 at 6:17 am
Besides, If there is a column in both tables which have same values but all other columns could have different values then this is the solution which may outperform the FULL OUTER JOIN solution for huge tables with lots of columns to be compared. For e.g If in both tables Column Aa have the same values then
DECLARE @a TABLE (Aa INT, Ab VARCHAR(5), Ac INT)
DECLARE @b-2 TABLE (Aa INT, Ab VARCHAR(5), Ac INT)
INSERT @a
(
[Aa]
,[Ab]
,[Ac]
)
SELECT 1, 'a', 1001 UNION ALL SELECT 2, 'b', 1002 UNION ALL SELECT 3, 'c', 1003 UNION ALL SELECT 4, 'd', 1001
INSERT @b-2
(
[Aa]
,[Ab]
,[Ac]
)
SELECT 1, 'a', 1001 UNION ALL SELECT 2, 'ab', 1002 UNION ALL SELECT 3, 'cd', 1003 UNION ALL SELECT 4, 'd', 1001
SELECT MIN(Aa)
, Ab
, Ac
FROM ( SELECT [Aa]
, [Ab]
, [Ac]
FROM @a AS A
UNION ALL
SELECT [Aa]
, [Ab]
, [Ac]
FROM @b-2 AS B
) AllValues
GROUP BY [Ab], [Ac]
HAVING COUNT(*) = 1
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply