Duplicate Record In Result
-
i have problem in my below query.. when i select record separately.. 2 records shown as desired. but when i make a join between them result shows 4 rows instead of 2 !
SELECT r3.Objective_Impacted_Detail, r3.Objective_Impacted_Title, r4.Legal_Compliance_Impacted_Title, r4.Legal_Compliance_Impacted
FROM dbo.Risk_00_03 AS r3
INNER JOIN dbo.Risk_00_04 AS r4 ON r4.Risk_01_Id_Num = r3.Risk_01_Id_Num
WHERE r3.Risk_01_Id_Num = '201600015'
joined tables result
separate 1st table result
separate 2nd table result
-
You have two rows in each table where Risk_01_Id_Num = '201600015'. Your result set with four rows is expected behaviour. If you wish to limit the result set to two rows, then you first have to decide from which table you will take two rows, and from which you will take only one: although Risk_01_Id_Num is the same for two rows in each table, there are column differences. Use those differences to decide which row to take. There are numerous ways of achieving this in TSQL, but first you must decide.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (8/23/2016)
You have two rows in each table where Risk_01_Id_Num = '201600015'. Your result set with four rows is expected behaviour. If you wish to limit the result set to two rows, then you first have to decide from which table you will take two rows, and from which you will take only one: although Risk_01_Id_Num is the same for two rows in each table, there are column differences. Use those differences to decide which row to take. There are numerous ways of achieving this in TSQL, but first you must decide.why it is showing 4 rows ? should'nt it show 4 columns with 2 rows instead place legal comp columns after objected impacted columns?
-
Messi (8/23/2016)
ChrisM@Work (8/23/2016)
You have two rows in each table where Risk_01_Id_Num = '201600015'. Your result set with four rows is expected behaviour. If you wish to limit the result set to two rows, then you first have to decide from which table you will take two rows, and from which you will take only one: although Risk_01_Id_Num is the same for two rows in each table, there are column differences. Use those differences to decide which row to take. There are numerous ways of achieving this in TSQL, but first you must decide.why it is showing 4 rows ? should'nt it show 4 columns with 2 rows instead place legal comp columns after objected impacted columns?
This should show you why 4 rows are returned:
CREATE TABLE #Table1 (ID_t1 INT IDENTITY(1,1), Risk_01_Id_Num VARCHAR(10))INSERT INTO #Table1 (Risk_01_Id_Num) VALUES ('201600015'), ('201600015')
CREATE TABLE #Table2 (ID_t2 INT IDENTITY(1,1), Risk_01_Id_Num VARCHAR(10))
INSERT INTO #Table2 (Risk_01_Id_Num) VALUES ('201600015'), ('201600015')
SELECT *
FROM #Table1 t1
INNER JOIN #Table2 t2
ON t2.Risk_01_Id_Num = t1.Risk_01_Id_Num
If not, can you adjust the model to reflect your environment? Thanks.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply