January 19, 2006 at 9:34 am
My query contains a UNION ALL. I have verified that both tables (tblOne and tblTwo) being queried were loaded with the correct number of rows today. In addition, each side Joins with 2 other (static) tables. One is a simple join, the other a Left Outer Join.
If, for example, tblOne contains 23721 rows, my query is returning 23916 rows. If I remove the Left Join it returns the 23721 rows it's supposed to return.
The left side of the Join is TblOne and the right side, one of the static tables, is supposed to return "department". If no department is found, I still want to return that row, although I change the NULL to "No Dept" using a case statement.
I have identified a row that appears twice in the result set. It was NOT one of the rows with a NULL department.
Can you explain why I am returning some rows twice?
January 19, 2006 at 10:59 am
>>Can you explain why I am returning some rows twice?
Without the DDL including PKey and Unique constraint defs, and sample data, probably not.
If you get more rows that expected, it generally means that the columns you are joining on aren't as unique as you though they were.
January 19, 2006 at 11:01 am
You have not posted the query, the table structures and their relationship or the sub-set of data so this answer is based on just the post from above.
If you have a one to many relationship between tblOne and the other table, then you will get that many records back. And it will appear as duplicate in your select list if you are not selecting the column from the right side table to make the distinction between the two records. You can use a DISTINCT to avoid the dupes...example:
DECLARE @TBLONE TABLE (COL1 INT NOT NULL PRIMARY KEY, COL2 VARCHAR(10))
INSERT INTO @TBLONE VALUES (1, 'ABC')
INSERT INTO @TBLONE VALUES (2, 'XYZ')
INSERT INTO @TBLONE VALUES (3, 'TBF')
DECLARE @DEPT TABLE (PK_COL INT NOT NULL PRIMARY KEY, COL1 INT NOT NULL, DEPT_NAME VARCHAR(10) NOT NULL)
INSERT INTO @DEPT VALUES (1, 1, 'DEPT1')
INSERT INTO @DEPT VALUES (2, 1, 'DEPT1')
INSERT INTO @DEPT VALUES (3, 2, 'DEPT1')
SELECT A.COL1, A.COL2, B.DEPT_NAME
FROM @TBLONE A
LEFT OUTER JOIN @DEPT B
ON A.COL1 = B.COL1
--OUTPUT
COL1 COL2 DEPT_NAME
----------- ---------- ----------
1 ABC DEPT1
1 ABC DEPT1
2 XYZ DEPT1
3 TBF NULL
(4 row(s) affected)
Since the relationship is assumed to be 1-N between the two tables, and since there are two matching records in the right table for COL1 = 1 and since the query is only selecting DEPT_NAME from the right table and not PK_COL as well (which would have made the distinction in the records that are bheing fetched from the right table), you will get duplicate records at the top...you can avoid that by using a DISTINCT clause, example:
SELECT DISTINCT A.COL1, A.COL2, B.DEPT_NAME
FROM @TBLONE A
LEFT OUTER JOIN @DEPT B
ON A.COL1 = B.COL1
--OUTPUT
COL1 COL2 DEPT_NAME
----------- ---------- ----------
1 ABC DEPT1
2 XYZ DEPT1
3 TBF NULL
(3 row(s) affected)
January 19, 2006 at 11:02 am
*outdated post
January 19, 2006 at 11:37 am
Thank you all for your help. It turns out that the comment that the rows "aren't as distinct as you thought" was right-on. The static table holding the departments had duplicate rows. The user who provided it to me that way did so unwittingly.
Thanks again to all responders for the education.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply