January 7, 2015 at 10:01 am
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
January 7, 2015 at 10:17 am
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.
January 7, 2015 at 8:41 pm
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
January 8, 2015 at 2:04 am
Hi All,
Please help me in writing a query for above scenario.
January 8, 2015 at 7:28 am
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
January 8, 2015 at 11:55 am
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.
January 8, 2015 at 1:43 pm
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