VB.NET

VB.NET RSS

Compare 2 Tables - Return Rows VB 2008

Please support our VB.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: May 2007

Posts: 45

Reputation: cellus205 is an unknown quantity at this point

Solved Threads: 1

cellus205

Offline

Light Poster

Compare 2 Tables - Return Rows VB 2008

Jun 2nd, 2008

Hows it going everyone. Im trying to compare two tables, and display the rows that are not in both tables in a DataGrid. Im using Visual Basic 2008, and created a query in the TableAdapterManager, but when I try to call the query, it is not displaying the results. Shows an error to lessen constraints, but I disabled constraints. I would really appreciate any tips, Im still pretty new to VB. Heres what I have in the query.

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
SELECT     Table1.Path, Table1.Filename, Table1.Type, Table1.[key], Table2.Path AS Expr1, Table2.Filename AS Expr2, Table2.Type AS Expr3
FROM         Table1, Table2
WHERE     (Table1.Path <> Table2.Path) OR
                      (Table1.Filename <> Table2.Filename)
SELECT     Table1.Path, Table1.Filename, Table1.Type, Table1.[key], Table2.Path AS Expr1, Table2.Filename AS Expr2, Table2.Type AS Expr3
FROM         Table1, Table2
WHERE     (Table1.Path <> Table2.Path) OR
                      (Table1.Filename <> Table2.Filename)

•

Join Date: Jun 2007

Posts: 22

Reputation: dadelsen is an unknown quantity at this point

Solved Threads: 5

dadelsen

Offline

Newbie Poster

Re: Compare 2 Tables - Return Rows VB 2008

Jun 3rd, 2008

Do you want to
1) step throgh all rows of Table1, and show the row when there is no row with the same path AND filename in Table 2
2) and then do the same vice versa?

If yes, and DBMS is MS SQL Server or Oracle I would use a union:

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
/* paste this in MS Query Analyzer, use || instead of + for oracle */
DROP TABLE t1
DROP TABLE t2
CREATE TABLE t1(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
CREATE TABLE t2(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
INSERT INTO t1 VALUES ( 'P1', 'F1' )
INSERT INTO t1 VALUES ( 'P2', 'F2' )
INSERT INTO t2 VALUES ( 'P1', 'F1' )
INSERT INTO t2 VALUES ( 'P3', 'F3' )
 
SELECT sPath + sFilename FROM t1 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t2) 
UNION ALL
SELECT sPath + sFilename FROM t2 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t1)
 
 
------------------------------ 
P2F2
P3F3
(2 rows affected)
/* paste this in MS Query Analyzer, use || instead of + for oracle */
DROP TABLE t1
DROP TABLE t2
CREATE TABLE t1(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
CREATE TABLE t2(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
INSERT INTO t1 VALUES ( 'P1', 'F1' )
INSERT INTO t1 VALUES ( 'P2', 'F2' )
INSERT INTO t2 VALUES ( 'P1', 'F1' )
INSERT INTO t2 VALUES ( 'P3', 'F3' )

SELECT sPath + sFilename FROM t1 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t2) 
UNION ALL
SELECT sPath + sFilename FROM t2 WHERE spath + sfilename NOT IN 
   (SELECT spath + sfilename FROM t1)


------------------------------ 
P2F2
P3F3
(2 rows affected)