How to relate two tables?

[chdboy]

I have two tables
Employer
Employee

Employer Table:
I have one field
EmployerID (pk Key assigned to it)
Employee Table:
EmployerID(pk Key assigned to it)
IDEmployer(fk key assigned to it,and Allow Nulls:Checked)
Now my question is how to relate two tables ,that I will now that who works for who?

currently with my code nothing(No values are being returned)

 Public Function DGVload_testsql() As DataView
        Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
        con.Open()
        _string = "select Employer.Firstname,Employee.Firstname from Employee inner join Employer on Employee.IDEmployer = Employer.EmployerID"
        Dim SampleSource As New DataSet
        Dim TableView As DataView
        Try
            Dim SampleCommand As New SqlCommand()
            Dim SampleDataAdapter = New SqlDataAdapter()
            SampleCommand.CommandText = _string
            SampleCommand.Connection = con
            SampleDataAdapter.SelectCommand = SampleCommand
            SampleDataAdapter.Fill(SampleSource)
            TableView = SampleSource.Tables(0).DefaultView
            'Use colors in DataGridView
            Test_dgv_sql.GridColor = Color.Red
            Test_dgv_sql.CellBorderStyle = DataGridViewCellBorderStyle.None
            Test_dgv_sql.BackgroundColor = Color.LightGray

            Test_dgv_sql.DefaultCellStyle.SelectionBackColor = Color.Brown
            'DGV1.DefaultCellStyle.SelectionForeColor = Color.Yellow

            Test_dgv_sql.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]

            Test_dgv_sql.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            Test_dgv_sql.AllowUserToResizeColumns = False

            Test_dgv_sql.RowsDefaultCellStyle.BackColor = Color.Bisque
            Test_dgv_sql.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige

            Test_dgv_sql.ColumnHeadersDefaultCellStyle.BackColor = Color.DarkRed
            Test_dgv_sql.ColumnHeadersDefaultCellStyle.ForeColor = Color.DarkGray

            'cells and rows height in DataGridView
            Dim row As DataGridViewRow = Me.Test_dgv_sql.RowTemplate
            row.DefaultCellStyle.BackColor = Color.Bisque
            row.Height = 30
            row.MinimumHeight = 15

        Catch ex As Exception
            Throw
        End Try
        Return TableView
    End Function
End Class

[Reverend Jim]

In order to relate two tables they have to have a common column. You'll have to include the EmployerID as a column in the Employee table. You described the tables as follows:

Employer Table:
I have two field
EmployerID (pk Key assigned to it)
Employee Table:
EmployerID(pk Key assigned to it)
IDEmployer(fk key assigned to it,and Allow Nulls:Checked)

You said the Employer table has two columns but I see only one. The Employee table has only two fields and both look like the EmployerID. Set the tables up as follows (add more columns as needed)

Employer
EmployerID (PK)
LastName
FirstName

Employee
EmployeeID (PK)
LastName
FirstName
EmployerID (FK)

[chdboy]

I already have EmployerID as column in both the tables

Employer Table:
I have One field
EmployerID (pk Key assigned to it)

Employee Table:
EmployerID(pk Key assigned to it)
IDEmployer(fk key assigned to it,and Allow Nulls:Checked)

[Reverend Jim]

You have two fields in your Employee table. They both look (by name) like they contain the Employer ID. If you are going to show me the tables then please show all fields. Your primary key for the Employee table should not be EmployerID.

[chdboy]

I have more than two columns on my tables offcourse
sorry I cannot upload the screenshot because under upload attachments there is no brows button.Usually there are two.
I have right clicked on the column IDEmployer (Employee table)and click on relationships to assign fk to the column..I'm tell you because I have never assigned a fk to any column I hope I'm right.
and I have IDEmployer in the Employee table but before Firstname and Lastname

[Reverend Jim]

For example. If I have two tables

USE [mydb]
GO

/****** Object:  Table [dbo].[Employer]    Script Date: 02/17/2013 06:40:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employer](
    [EmployerID] [int] IDENTITY(1,1) NOT NULL,
    [LastName]   [varchar](50)       NOT NULL,
    [FirstName]  [varchar](50)       NOT NULL,
 CONSTRAINT [PK_Employer] PRIMARY KEY CLUSTERED 
(
    [EmployerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

and

USE [mydb]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 02/17/2013 06:40:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [LastName]   [varchar](50)       NOT NULL,
    [FirstName]  [varchar](50)       NOT NULL,
    [EmployerID] [int] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Employer] FOREIGN KEY([EmployerID])
REFERENCES [dbo].[Employer] ([EmployerID])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Employer]
GO

then I can do

SELECT Employer.FirstName,Employee.FirstName
  FROM Employer INNER JOIN Employee ON Employer.EmployerID = Employee.EmployerID

or

SELECT Employer.FirstName,Employee.FirstName
  FROM Employer,Employee
 WHERE Employer.EmployerID = Employee.EmployerID

[chdboy]

This post has no text-based content.

[Reverend Jim]

Why do you have both EmployerID and IDEmployer in the Employee table and why do you allow NULLs in IDEmployer?

[chdboy]

I have created IDEmployer column just to assign FK to it,and that's why it also allows null.....but now I have changed the fk to the EmployerID in Employee table and able to get result displayed in my datagrid atleast,and the thing I don't understand is how to make CONSTRAINT on the column and why this is being used?,and now how do I know that who works for who?

[Reverend Jim]

Try creating the two tables from my example above. EmployerID and EmployeeID are IDENTITY fields. You don't supply a value when you insert a new record. The value is generated by the database engine. You do, however, have to supply a value for EmployerID when you insert a new record into the Employee table. Because Employee.EmployerID was created with a foreign key constraint, any attempt to add an Employee record with a non-existing EmployerID will fail. A foreign key, therefore, cannot be NULL. The following command creats the constraint

ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employer] FOREIGN KEY([EmployerID])
REFERENCES  [dbo].[Employer] ([EmployerID])

[chdboy]

ok so how do we attach multipal tables or third table to Employee or Employer?What Field is must for the third table to be attached to these two?

[Reverend Jim]

It depends what the relationship is. You can relate tables based on any field.