Left Join Multiple Rows Because of Right Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Left Join Multiple Rows Because of Right Table

Author

Topic

rutherford218
Starting Member

7 Posts

Posted - 2006-09-11 : 11:30:11

I have a query, I am condensing to save on space, that has multiple rows returned on the same id. The problem is from a Left join on the right table, so I get duplicate rows, I want to know how to just get 1 result from the right table no matter what, even if it have 5 records appended to the left table's id. Example...

select table1.id, table1.name, table2.comments from table1
left join table2 on table1.id = table2.id

Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 11:32:24

Either you want all the rows from TABLE2, or you want some aggregate from TABLE2 ...

If you select table2.comments and there are 5 rows, say, in TABLE2 with ID matching TABLE1 what would you like retrieved?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-11 : 11:37:03

You need to post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

rutherford218
Starting Member

7 Posts

Posted - 2006-09-11 : 11:38:16

aggregate would be nice, but I can have only one row returned as well. Either way my main goal is to just get rid of the duplicate values.

Thanks

rutherford218
Starting Member

7 Posts

Posted - 2006-09-11 : 11:43:40

a sample would be like this

table1.id table1.name table2.commments
1 this new comment
1 this new commment 1
2 that comment
3 other comment
3 other comment 1
3 other comment 2

I am trying to get it so there is only 1 id returned no matter haw many references there are to that id in the second table.

Thanks

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 11:48:26

One of us isn't understanding each other!

If you join TABLE1 and TABLE2 and there are two rows in TABLE2 [for a specific ID value] which do you want to see from TABLE2?

If you want both rows from TABLE2, so for example you can display ALL the comments to the user, then you need to retrieve both rows - or you need to concatenate all the comments, or display one of them using MIN, MAX or some-other-aggregate function.

Sample data, and an example of the result you want, would help us - as Madhi suggested.

Kristen

rutherford218
Starting Member

7 Posts

Posted - 2006-09-11 : 11:53:23

an example of the data would be,

table1.id, table1.name, table2.commments
1, this, new comment
1, this, new commment 1
2, that, comment
3, other, comment
3, other, comment 1
3, other, comment 2

the ideal place to be with the data would be here...time permitting.

table1.id, table1.name, table2.commments
1, this, new comment commment 1
2, that, comment
3, other, comment comment 1 comment 2

but I could still do with this.

table1.id table1.name table2.commments

1 this new comment
2 that comment
3 other comment

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 12:29:08

If you want to concatenate all the comments your best bet would be to use a function (i.e. in the SELECT, not a JOIN'd table). The function will be constrained by the VARCHAR(8000) limit in SQL 2000, so this won't work if you need more than 8,000 characters (SQL 2005 allows more)


CREATE FUNCTION dbo.FN_Table2Comments
(
	@ID	int
)
RETURNS varchar(8000)
AS
/*
 * FN_Table2Comments	Concatenated List of Comments for a given ID
 */
BEGIN
DECLARE @String	varchar(8000)

	SELECT	@String = COALESCE(@String + ' ', '') 
		+ comment
	FROM	dbo.table2
	WHERE	id = @ID
	ORDER BY CommentNumber	-- Something that ensures that the order is repeatable

	RETURN @String
END
GO

then you can do:

select table1.id, table1.name, dbo.FN_Table2Comments(id)
from table1

If you only want just the first comment (where "first" is the Lowest Comment Number, or the earliest Date, or somesuch) then you need to JOIN to a Nested Sub Query. Basically the sub query will select just the first comment for each table, and the you "join" to that. Note that you will need a unique key for each comment - so ID + MIN(EntryDate) will require that there can be NO duplicates for EntryDate - if you have got an IDENTITY column in TABLE2 then that will be unique and you can use that, of course.


select table1.id, table1.name, table2.comments 
from table1 AS T1
     left join 
     (
         SELECT ID, comments
         FROM   table2 AS T2a
         JOIN
         (
             SELECT ID, [MIN_CommentNo] = MIN(CommentNo)
             FROM    table2 
         ) AS T2b
              ON T2b.ID = T2a.ID
             AND T2b.MIN_CommentNo = T2a.CommentNo
    ) AS T2
        ON T2.id = T1.id

Kristen

rutherford218
Starting Member

7 Posts

Posted - 2006-09-11 : 13:22:03

I took the db_finction example and modified it for my query, worked like a champ. Thanks a lot for all of your help and patience.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources