filtering same values in one field in 2 tables

SELECT * from table2 where exists(select MFG from table1 where MFG.table1 = MFG.table2)

would this be correct ?

This code works if what exists in table2 exists in table1 also and that's displayed in my datagridview.. but how do i do this for my other three fields ? is there way by which i can use the AND operator and do something?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Final Database.mdb")

        con.Open()

        Dim cmd As New OleDbCommand

        cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION from table2 where exists(select * from table1 where table2.GRAPHICSVERSION=table1.GRAPHICSVERSION)", con)


        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

        Try
            Dim ds As DataSet = New DataSet()

            da.Fill(ds, "table2")

            DataGridView2.DataSource = ds.Tables("table2").DefaultView

        Finally

            con.Close()

            cmd = Nothing

            da.Dispose()

            con.Dispose()

        End Try

        con.Close()

        cmd = Nothing

con.Dispose()

End Sub

End Class

i have both matching data and not matching data.. i want to get the matching data in one color adn the not matching data in another..can i do that here ?

can u please help me? i have been trying to find a solution for weeks now..!!

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Final Database.mdb")

con.Open()

Dim cmd As New OleDbCommand

cmd = New OleDbCommand("SELECT DISTINCT MAKE from table2 where exists(select * from table1 where table2.MAKE = table1.MAKE)union select distinct MAKE from table2 where not exists(select * from table1 where table2.MAKE = table1.MAKE)", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

Try

Dim ds As DataSet = New DataSet()

da.Fill(ds, "table2")

DataGridView2.DataSource = ds.Tables("table2").DefaultView

Finally

con.Close()

cmd = Nothing

da.Dispose()

con.Dispose()

End Try

con.Close()

cmd = Nothing

con.Dispose()

End Sub

FOLKS.. i am trying to compare the field's values in 2 tables: table1 and table2. if a field's value in table2 exists in table1, i want it in green else red.

I am trying to achieve this but i get the error : "oledb exception was unhandled" and "Ierrorinfo.get description.failed with E_FAIL(0X80004005)" at the line da.fill(ds,"table2")

this error normally comes up when the fieldnames used SQL keywords but i dont think there is a keyword in my Query that I am using.. i ahev been trying to get this solved for weeks with no result..i would appreciate if you could help me with this..

thanks
Kavs

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Final Database.mdb")

        con.Open()

        Dim cmd As New OleDbCommand

        cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when GRAPHICSVERSION IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

        Try
            Dim ds As DataSet = New DataSet()

            da.Fill(ds, "table2")

            DataGridView2.DataSource = ds.Tables("table2").DefaultView

        Finally

            con.Close()

            cmd = Nothing

            da.Dispose()

            con.Dispose()

        End Try

        con.Close()

        cmd = Nothing

        con.Dispose()

    End Sub

I think you have to qualify the field, "GRAPHICSVERSION" in the case statement.

You have:

cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when [B]GRAPHICSVERSION[/B] IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

I think you need something like this:

cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when [B]table1.GRAPHICSVERSION[/B] IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

hi Mike.. I am using Access 2003 and VB 2005.. but the code that i have put up works in sql and I dont think its' used in access..can u pls help me!?

Sorry - I haven't touched Access in ages so I don't think I can add any more value to this thread.

Perhaps the only advice I can give is to open up Access and test the query within the Access environment. Otherwise, use some of the Access wizards to see what kind of code it generates. Maybe this will give you a starting point.

Sorry I can't help any further.

np Mike..thanks..