Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    14

    Unanswered: How can I realte two sql tables?

    Hi,

    I have two tables
    Employer
    Employee
    How can I relate the two tables ,so that I know that who works for who.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163

    Tmi - not

    The only answer that can be given with the information you have provided is to study up on foreign keys.

    If you want an answer that includes the SQL code to create the foreign key for your tables, you will need to post the DDL for the two tables in question in order to get that.

  3. #3
    Join Date
    Nov 2012
    Posts
    14
    Here is the code
    Code:
    _string = "select Employer.CompanyName,Employee.Firstname from Employee inner join Employer on Employee.IDEmployer = Employer.EmployerID"
    I'm getting the Field and Filed names ,but with no values in it.Employer Table has EmployerID Field with P_K key assigned to it,and Employee table with the same field assigned P_K kay assigned to it,and F_K key assigned to IDEmployer(Employee table)

    here is the screen shot
    Attached Thumbnails Attached Thumbnails Sql_Join_error.jpg  
    Last edited by chdboy; 02-17-13 at 02:45.

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by chdboy View Post
    Here is the code
    Code:
     "select Employer.CompanyName,Employee.Firstname
      from Employee 
      inner join Employer on Employee.IDEmployer = Employer.EmployerID"
    I'm getting the Field and Filed names ,but with no values in it.Employer Table has EmployerID Field with P_K key assigned to it,and Employee table with the same field assigned P_K kay assigned to it,and F_K key assigned to IDEmployer(Employee table)

    here is the screen shot
    You are posting contradicting information here.

    You state that your tables, Employee & Employer each have the same column name of EmployerID. Yet the Select statement shows different column names.

    Please post the DDL for both tables. Go to each table in SSMS, right click on it, select 'Script Table as' Create to New Query Window. Clean it up and paste that into a reply here.

    If I had to venture a guess as to what you are shooting for, try something like this:

    Code:
    SELECT Employer.CompanyName, Employee.FirstName
      FROM Employer
     INNER JOIN Employee on Employee.IDEmployer = Employer.EmployerID
    The FK could be something like:

    Code:
    ALTER TABLE Employee  WITH CHECK 
    ADD CONSTRAINT EmployeeID_FX
    FOREIGN KEY(IDEmployer)
    REFERENCES Employer (EmployerID)
    ON UPDATE CASCADE
    My personal preference is to query from the table that would be higher in the hierarchy whenever possible. This makes thinking about the inner join easier for me. In your example, I would be thinking, give me all employees who's ID matches the employer ID and if it can't find one, don't include it!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you get from running the following two queries (post the results):

    Code:
    select	COUNT(*) as EmployeeCount,
    	COUNT(distinct IDEmployer) as EmployerCount,
    	MIN(IDEmployer) as LowestEmployerID,
    	MAX(IDEmployer) as HighestEmployerID
    from	Employee
    		
    select	COUNT(*) as EmployerCount,
    	COUNT(distinct EmployerID) as EmployerIDs,
    	MIN(EmployerID) as LowestEmployerID,
    	MAX(EmployerID) as HighestEmployerID
    from	Employer
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •