Select all data the doesn't have a match from another table

[jared.geli]

Good day to all I was wondering if it is possible to show all data in a datagridview that doesn't match from 2 tables?
Like Inner Join if you have at least 1 match data from the other table it will display it. What i want is to display
those data that doesn't have a match from the other table.

Here's my code:

Dim cmd As OleDbCommand = New OleDbCommand("SELECT Data1.ID, Data1.DateFile as [Date File] , Data1.Plate as [Plate #], Data1.Driver as Driver, Data1.EmpID as EmpID, Data1.OdometerBefore As [Odometer Before], Data1.OdometerAfter As [Odometer After], Data1.Gas as Gas, Data1.CostofGas as [Cost of Gas] FROM Data1 Inner Join Data4 on Data1.Plate=Data4.Plate", con)

[ReyJEnriquez]

instersection you mean??...

[jared.geli]

Kinda but I want all the non matching data to show up instead.
Here's the scenario:
There are 2 tables one is Data1 which hold the daily record for
trips and the other one is Data4 which holds the current driver assigned
for the vehicle. I know to filter the current Drivers, Employee
ID and Plate# I want to dispay all the past record of the Plate# that were handled by other drivers

[ReyJEnriquez]

show us the Data in the table1 and table4 first

Like this format..

Table1
dailyRecord
....
....
..

Table4
driver'sName
....
...
..

[jared.geli]

Table1
ID - "Primary number. Hidden. Auto-Increment"
Datefile
Plate
Driver
EmpID
OdometerBefore
OdometerAfter
Gas
CostofGas

Table2
EmpID
Driver
Plate

[ReyJEnriquez]

I guess if you want to display the past plate#, you should do something to the datefile..

Retrieve a specific date, in that way you can tell that in that date...

[jared.geli]

I want to display the records from Table1 that doesn't have a match in Table2.
Table2 is the record for the current drivers.
Example

Table1
DateFile  Plate  Driver EmpID OdometerBefore OdometerAfter Gas CostofGas
10/2/2013 DRG369 Jared  00161 4579            9689         15  4370
10/3/2013 ABC123 Jared  00161 3000            5760         5   250
10/3/2013 ASD456 Jonel  00376 5078            8907         10  3000
10/4/2013 DRG369 Ichi   00456 3686            6907         7   750

Table2
EmpID  Driver  Plate
00161  Jared   ABC123
00376  Jonel   ASD456
00456  Ichi    DRG369

I've tried using Inner Join and I've successfully display the record in Table1
that has a match from Table2. Now I want is to display the record from
Table1 where it does not have a match in Table2. I tried using Full Outer
but it gives me an OleDbException of "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)"

Here's my code as of now:

If ComboBox1.Text = "Trips" And ComboBox2.Text = "Past Driver" Then
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;data source=G:\Office\VehicleAltered.accdb")
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT Table1.ID, Table1.DateFile as [Date File], Table1.Plate as [Plate #], Table1.Driver as Driver, Table1.EmpID as EmpID, Table1.OdometerBefore As [Odometer Before], Table1.OdometerAfter As [Odometer After], Table1.Gas as Gas, Table1.CostofGas as [Cost of Gas] FROM Table1 Full Outer Join Table2 on Table1.EmpID=Table2.EmpID and Table1.Plate=Table2.Plate", con)
            con.Open()
            adpt = New OleDbDataAdapter(cmd)
            'Here one CommandBuilder object is required.
            'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  
            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
            DtSet = New DataSet()
            adpt.Fill(DtSet, "Table1")
            DataGridView1.DataSource = DtSet.Tables("Table1").DefaultView
            DataGridView1.Columns(0).Visible = False
            con.Close()
            con = Nothing
        End If

[ReyJEnriquez]

Do you have your stored procedure??..

[jared.geli]

no I don't have

[ReyJEnriquez]

you should have one..

[ReyJEnriquez]

you are making it altogether and that makes it more confusing...

[jared.geli]

Thanks for replying I already figured out how to. All I need to do is Left Join with the two values I need to base are null instead of Full Outer Join.

Here's the code so it can help others who are having the same problem

SELECT Table1.Fieldname1, Table1.Fieldname2  FROM Table1 Left Join Table2 on Table1.Fieldname1=Table2.Fieldname1 and Table1.Fieldname2=Table2.Fieldname2 where Table2.Fieldname1 is null and Table2.Fieldname2 is null