Join Query returning duplicated rows
-
SELECT
[Sheet1$].Sheet1DetailId,
[Sheet1$].Sheet1Id,
[Sheet2$].Sheet2Id,
LTrim(RTrim([Sheet3$].Mission)) + '-' + CStr(Format('01-' + [Sheet3$].Sheet3Date,"mm/dd/yyyy")) AS Sheet3DateColumn,
[Sheet1$].one,
[Sheet1$].two,
[Sheet1$].three,
[Sheet1$].four,
[Sheet1$].five,
[Sheet1$].six,
[Sheet1$].seven,
[Sheet1$].eight,
[Sheet1$].nine,
[Sheet1$].ten,
LTRIM(RTRIM([Sheet2$].Name)) AS Sheet2Name
FROM (([Sheet1$])
INNER JOIN [Sheet2$] ON [Sheet1$].Id = [Sheet2$].Sheet2Id)
INNER JOIN [Sheet3$] ON [Sheet1$].Id = [Sheet3$].Sheet3IdIn SSIS 2005 Excel source, i have set the data access mode to SQL command and typed in the above query to get data from 3 Microsoft excel sheets(sheet1, sheet2, sheet3). The intention is to get all rows in sheet1 with their corresponding reference values in sheets 2 & 3 but for some strange reason, every row in sheet1 is being returned twice. I can't seem to figure out what i am doing wrong in query?
"Coming soon"
-
SELECT
[Sheet1$].Sheet1DetailId,
[Sheet1$].Sheet1Id,
[Sheet2$].Sheet2Id,
LTrim(RTrim([Sheet3$].Mission)) + '-' + CStr(Format('01-' + [Sheet3$].Sheet3Date,"mm/dd/yyyy")) AS Sheet3DateColumn,
[Sheet1$].one,
[Sheet1$].two,
[Sheet1$].three,
[Sheet1$].four,
[Sheet1$].five,
[Sheet1$].six,
[Sheet1$].seven,
[Sheet1$].eight,
[Sheet1$].nine,
[Sheet1$].ten,
LTRIM(RTRIM([Sheet2$].Name)) AS Sheet2Name
FROM (([Sheet1$])
INNER JOIN [Sheet2$] ON [Sheet1$].Id = [Sheet2$].Sheet2Id)
INNER JOIN [Sheet3$] ON [Sheet1$].Id = [Sheet3$].Sheet3IdIn SSIS 2005 Excel source, i have set the data access mode to SQL command and typed in the above query to get data from 3 Microsoft excel sheets(sheet1, sheet2, sheet3). The intention is to get all rows in sheet1 with their corresponding reference values in sheets 2 & 3 but for some strange reason, every row in sheet1 is being returned twice. I can't seem to figure out what i am doing wrong in query?
"Coming soon"
What I normally do i this case is to comment out the joins until I identify which one is causing the duplicates, then I filter to 1 row and go hunting through the data to find the cause.
Never underestimate the power of human stupidity RAH
-
SELECT
[Sheet1$].Sheet1DetailId,
[Sheet1$].Sheet1Id,
[Sheet2$].Sheet2Id,
LTrim(RTrim([Sheet3$].Mission)) + '-' + CStr(Format('01-' + [Sheet3$].Sheet3Date,"mm/dd/yyyy")) AS Sheet3DateColumn,
[Sheet1$].one,
[Sheet1$].two,
[Sheet1$].three,
[Sheet1$].four,
[Sheet1$].five,
[Sheet1$].six,
[Sheet1$].seven,
[Sheet1$].eight,
[Sheet1$].nine,
[Sheet1$].ten,
LTRIM(RTRIM([Sheet2$].Name)) AS Sheet2Name
FROM (([Sheet1$])
INNER JOIN [Sheet2$] ON [Sheet1$].Id = [Sheet2$].Sheet2Id)
INNER JOIN [Sheet3$] ON [Sheet1$].Id = [Sheet3$].Sheet3IdIn SSIS 2005 Excel source, i have set the data access mode to SQL command and typed in the above query to get data from 3 Microsoft excel sheets(sheet1, sheet2, sheet3). The intention is to get all rows in sheet1 with their corresponding reference values in sheets 2 & 3 but for some strange reason, every row in sheet1 is being returned twice. I can't seem to figure out what i am doing wrong in query?
"Coming soon"
Dear, It happens only when the multiple records are available with the correspondence of [Sheet1$].Id into the [Sheet2$] ([Sheet2$].Sheet2Id) Or [Sheet3$] ([Sheet3$].Sheet3Id). Please check with the multiple records into the Both the Sheet 2 & 3. If you dont want to remove the multiple records from those sheets then Add distinct clause into the SELECT Query.
SELECT DISTINCT
[Sheet1$].Sheet1DetailId,
[Sheet1$].Sheet1Id,
[Sheet2$].Sheet2Id,
LTrim(RTrim([Sheet3$].Mission)) + '-' + CStr(Format('01-' + [Sheet3$].Sheet3Date,"mm/dd/yyyy")) AS Sheet3DateColumn,
[Sheet1$].one,
[Sheet1$].two,
[Sheet1$].three,
[Sheet1$].four,
[Sheet1$].five,
[Sheet1$].six,
[Sheet1$].seven,
[Sheet1$].eight,
[Sheet1$].nine,
[Sheet1$].ten,
LTRIM(RTRIM([Sheet2$].Name)) AS Sheet2Name
FROM (([Sheet1$])
INNER JOIN [Sheet2$] ON [Sheet1$].Id = [Sheet2$].Sheet2Id)
INNER JOIN [Sheet3$] ON [Sheet1$].Id = [Sheet3$].Sheet3Id