How to compare columns from three tables using Inner Join

  • Hi All,

    Please advise how to compare columns from three tables using inner join.

    For example Tables are table_a (column 1) table_b(Column1) and table_c(Column1).

    I have come up with below query

    Select column1 from table_a inner join table_b on table_a.column1=table_b.column1 inner join table_c on table_b.column1=table_c.column1

    Will this sql list out the rows which has matching columns found on all three tables.

    Please provide a clear example for understanding how to compare cplumns from three tables using inner Join

  • One example could come from AdventureWorks, can be found at http://msftdbprodsamples.codeplex.com/releases/view/93587. For this I am looking at the three tables EmployeeDepartmentHistory, Department and Shift from the HR tables in the 2008 version. The tables Department and Shift do not have a key connecting them. However, if I join the Department table to EmployeeDepartmentHistory, I can then join EmployeeDepartmentHistory to the Shift table. This would now allow me to select data from both Department and Shift like below.

    SELECT Department.Name, Shift.Name

    FROM Department

    INNER JOIN EmployeeDepartmentHistory

    ON Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID

    INNER JOIN Shift

    ON EmployeeDepartmentHistory.ShiftID = Shift.ShiftID

    Please let me know if this answers your question or if there is something additional that could help.

  • Hi,

    Sorry for late reply, I got disconnected from Net. I have tried the inner Join, But The expected output is not coming. Please advise.

    Please check the below scenario.

    I have three tables which has data as below .

    Source Table

    RequestNo code idNo

    123 4 56

    124 4 45

    343 5 565

    Check1 Table

    RequestNo code idNo

    124 4 56

    Check2 Table

    RequestNo code idNo

    343 5 565

    I want to know to display the rows in Source Table which has a ClaimNumber that matches with either Check1 table or Check2 table.

    I wrote below query based on the suggestion given

    select * from Source A INNER JOIN Check1 B ON A.RequestNo = B.RequestNo INNER JOIN Check3 C ON A.RequestNo=C.RequestNo

    After running this query the output Im getting is

    RequestNo code idNo

    124 4 56

    But the Row which matches in the Check2 table is not displaying, only Check1 table in which a match is found is displaying.

    Please give an example of how to display the records in Source table which has matching records in Both Check1 table and Check2 Table using INNER JOIN

  • Hi All,

    Please help me in writing a query for above scenario.

  • The issue you are running into is that the inner joins are trying to combine the records that meet both of ON clauses. My suggestion would be to use left joins for this kind of problem.

    select * from Source A

    LEFT JOIN Check1 B ON A.RequestNo = B.RequestNo

    LEFT JOIN Check2 C ON A.RequestNo=C.RequestNo

    WHERE B.RequestNo is not null OR C.RequestNo is not null

  • Hi GiraffeDBA,

    Thanks verymuch !!!

    Worked perfectly.

    The issue you are running into is that the inner joins are trying to combine the records that meet both of ON clauses

    Could you please elaborate this, what I understood is first it looks for a match using the first ON CLAUSE and then it looks for the matching record in the check2 table which has the same record which matched in check1 table.

    That's y it is not fetching the match in check2 table.

    Am I clear in understanding.

    And also in the ON clause, can I use more than one column comparison, can I check for two or more column comparison using AND ?

    Please advise.

  • The way that an INNER JOIN works is that it returns the values that are present in both tables and only the intersecting records are returned. For each additional join that you add onto the query it creates another comparison with the newly added table. Any records that contain a NULL value for the column that is used in the ON clause will not be returned.

    In your example, check1 will return a NULL for the RequestNo 123 and 343, and check 2 will return a NULL for the RequestNo 123 and 124. Since they return a NULL value in a column used in the ON clauses, all three rows should be excluded from the result.

    You said that it returned one row which I find odd since I recreated it on my own and received no rows. The script I tested with is below if you would like to try it.

    As for what is valid in the ON clause, you are able to combine multiple columns in the ON clause just by adding an AND followed by another comparison. You are also able to use OR.

    CREATE TABLE dbo.Source(RequestNo int,code int,idNo int);

    INSERT INTO dbo.Source([RequestNo],,[idNo])

    VALUES

    (123,4,56),

    (124,4,45),

    (343,5,565);

    CREATE TABLE dbo.Check1(RequestNo int,code int,idNo int);

    INSERT INTO dbo.Check1

    VALUES

    (124,4,45);

    CREATE TABLE dbo.Check2(RequestNo int,code int,idNo int);

    INSERT INTO dbo.Check2

    VALUES

    (343,5,565);

    select * from Source A

    INNER JOIN Check1 B ON A.RequestNo = B.RequestNo

    INNER JOIN Check2 C ON A.RequestNo=C.RequestNo

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply