How do I get rid of these duplicate rows...

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)

How do I get rid of these duplicate rows...

Author

Topic

andreamc
Starting Member

3 Posts

Posted - 2009-07-06 : 15:58:21

Hi guys,
This is my first time posting on here so let me know if I've posted this in the wrong spot or done something else entirely stupid.

I work on a university doing the room assignments for the residence halls. We are about to do mailings to each student to let them know who their roommates are, but I'm having a set back.
I just need to create a view that on the most basic level has the student's name and each of their roommate's names. Each student will have 0, 1, or 2 roommates so it should be able handle each situation, just returning a null or blank if there is no roommate.

My query is below. I do a left outer join on the same table twice to pull each of the possible roommates. The problem is that if Joey has two roommates, Fred and George, it will return two rows. So I end up with this:
Joey | Fred | George
Joey | George | Fred

I just want ONE of those rows. Any way to accomplish this? I have had "TOP 1" suggested but I can't find any way to make that work with my view.

SELECT N.Name, M.Name AS Roommate1, O.Name AS Roommate2
FROM NextTermReservations N
LEFT OUTER JOIN NextTermReservations M ON N.StudentID <> M.StudentID AND N.HallID = M.HallID AND N.Room = M.Room
LEFT OUTER JOIN NextTermReservations O ON O.StudentID <> M.StudentID AND O.StudentID <> N.StudentID AND N.HallID = O.HallID AND N.Room = O.Room

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-06 : 17:50:31

Happy to help but a couple questions first:

- Do you want one row per room or one row per student? (ie: one row for Joey, one for Fred and one for George OR just one row?)

- Can you confirm that you are using sql server version 2000? (as that is the forum you posted in).

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-06 : 18:04:30

Assuming one row per student and if you don't mind a slightly different structure here is one possibility using your existing query:


select d.name
       ,max(roommates) as roommates
from   (
       SELECT N.Name, coalesce(M.Name + ', ' + o.name, m.name, o.name)  AS Roommates
       FROM NextTermReservations N
       LEFT OUTER JOIN NextTermReservations M ON N.StudentID <> M.StudentID AND N.HallID = M.HallID AND N.Room = M.Room
       LEFT OUTER JOIN NextTermReservations O ON O.StudentID <> M.StudentID AND O.StudentID <> N.StudentID AND N.HallID = O.HallID AND N.Room = O.Room
       ) d
group by name

Be One with the Optimizer
TG

andreamc
Starting Member

3 Posts

Posted - 2009-07-06 : 18:34:07

Hi TG thanks for your reply,

I am looking for one row per student. We are currently using 2000, but I am going to upgrade to 2008 as soon as the higher-ups give me the OK. So for now, 2000 functionality would be best but if there are any tips that would make this easier in 2008 I would welcome them.

Using coalesce is a good thought, but I need them separated out so they can be used in a mail merge.
If there aren't any other options I might have to use that and just split it in excel... it would be easier than deleting every other row for 4000 records I guess.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-06 : 18:49:55

>>If there aren't any other options...
There is always options :) But I only coalesced the roommates. The mail merge target is in it's own column. But if that still doesn't work for you post back...

Be One with the Optimizer
TG

os_yadav
Starting Member

4 Posts

Posted - 2009-07-07 : 06:37:55

it is possible through SP if you want then i can tell u

SYSTEM ANALYST

os_yadav
Starting Member

4 Posts

Posted - 2009-07-07 : 06:48:25

CREATE Procedure roomates
AS
BEGIN
CREATE TABLE #temp
(
name varchar(10),
room int
)

INSERT INTO #temp
SELECT 'name1',1
UNION ALL
SELECT 'name2',1
UNION ALL
SELECT 'name3',1
UNION ALL
SELECT 'name4',2
UNION ALL
SELECT 'name5',2
UNION ALL
SELECT 'name6',3

create table #temp1
( room int,
name1 varchar(10),
name2 varchar(10),
name3 varchar(10)
)

insert into #temp1 (room)
SELECT distinct room from #temp

update #temp1
set name1 = b.name
from #temp1 a, #temp b
where a.room = b.room

update #temp1
set name2 = b.name
from #temp1 a, #temp b
where a.room = b.room
and b.name <> a.name1

update #temp1
set name3 = b.name
from #temp1 a, #temp b
where a.room = b.room
and b.name <> a.name1
and b.name <> a.name2

SELECT * from #temp

SELECT room,replace(isnull(ltrim(rtrim(name1)),'')+','+isnull(ltrim(rtrim(name2)),'')+','+isnull(ltrim(rtrim(name3)),''),',,',',')
from #temp1

end

SYSTEM ANALYST

Hans1963
Starting Member

2 Posts

Posted - 2009-07-07 : 07:30:50

Why use an SP if it can be done in plain SQL?

andreamc
Starting Member

3 Posts

Posted - 2009-07-07 : 09:33:26

Thanks for the suggestions.

TG, I just tried your query and it actually has the same problem mine did. Now I get
Joey | Fred, George
Joey | George, Fred
Those two joins are bringing back the same names in different orders so I'm pretty sure I need to change how I am thinking about this... maybe the joins can't do what I need?

os_yadav, I would prefer a view but if I can't get it to work within the next few days I will probably go with a function and modify your code. Thanks for writing it up!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-07 : 11:02:52

quote:
Originally posted by andreamc

Thanks for the suggestions.

TG, I just tried your query and it actually has the same problem mine did. Now I get
Joey | Fred, George
Joey | George, Fred

That is not what I got:


use tempdb
go
create table NextTermReservations (studentid int, hallid int, room int, name varchar(25))
go
insert NextTermReservations
select 1,1,102,'Joey' union all
select 2,1,102,'George' union all
select 3,1,102,'Fred'

go

select d.name
       ,max(roommates) as roommates
from   (
       SELECT N.Name, coalesce(M.Name + ', ' + o.name, m.name, o.name)  AS Roommates
       FROM NextTermReservations N
       LEFT OUTER JOIN NextTermReservations M ON N.StudentID <> M.StudentID AND N.HallID = M.HallID AND N.Room = M.Room
       LEFT OUTER JOIN NextTermReservations O ON O.StudentID <> M.StudentID AND O.StudentID <> N.StudentID AND N.HallID = O.HallID AND N.Room = O.Room
       ) d
group by name

go
drop table NextTermReservations

OUTPUT:
name                      roommates
------------------------- ----------------
Fred                      Joey, George
George                    Joey, Fred
Joey                      George, Fred

I probably wouldn't have done it this way from scratch but was just re-using your code. Again if this output doesn't work for you we can start over and seperate all your columns. If you still aren't getting this output then post your actual code.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-07 : 11:56:13

For Sql Server 2000 I probably would use a helper function to get the room-mates. Here is an example for both a single csv as well as individual columns. The final result is a view.


use tempdb
go
create table NextTermReservations (studentid int, hallid int, room int, name varchar(25))
go
insert NextTermReservations
select 1,1,102,'Joey' union all
select 2,1,102,'George' union all
select 3,1,102,'Fred' union all
select 4,1,104,'John' union all
select 5,1,104,'Paul' union all
select 6,1,105,'Ringo' 

go
--Create a function to get a CSV of Student's roommates
create function dbo.getRoommates(@studentid int, @hallid int, @room int)
returns varchar(200)
as
begin
       declare @out varchar(200)
       select @out = coalesce(@out + ', ' + name, name)
       from   NextTermReservations
       where  hallid = @hallid
       and    room = @room
       and    studentid != @studentid

       return @out
end
go
--Create a function to get a Student's specific roommate
create function dbo.getRoommateNo(@studentid int, @hallid int, @room int, @whichRoommate int)
returns varchar(25)
as
begin
       declare @out varchar(25)

       select @out = name
       from   (
              select name
                     ,seq = (select count(*) 
                            from NextTermReservations 
                            where hallid = @hallid 
                            and room = @room 
                            and studentid != @studentid 
                            and studentid <= n.studentid)
              from   NextTermReservations n
              where  hallid = @hallid
              and    room = @room
              and    studentid != @studentid
              ) d
       where  seq = @whichRoommate

       return @out
end
go

--Your VIEW
create view vwRoomAssignmentsByStudent
as
select Hallid
       ,room
       ,studentid
       ,name
       ,[RoomMate1] = dbo.getRoommateNo(studentid, hallid, room, 1)
       ,[RoomMate2] = dbo.getRoommateNo(studentid, hallid, room, 2)
       ,[Your Roommates] = dbo.getRoommates(studentid, hallid, room)
from   NextTermReservations
group by Hallid
       ,room
       ,studentid
       ,name
go

select * from vwRoomAssignmentsByStudent

go
drop view vwRoomAssignmentsByStudent
drop function getRoommates
drop function getRoommateNo
drop table NextTermReservations

go

OUTPUT:

Hallid      room        studentid   name                      RoomMate1                 RoomMate2                 Your Roommates
----------- ----------- ----------- ------------------------- ------------------------- ------------------------- -------------------
1           102         1           Joey                      George                    Fred                      George, Fred
1           102         2           George                    Joey                      Fred                      Joey, Fred
1           102         3           Fred                      Joey                      George                    Joey, George
1           104         4           John                      Paul                      NULL                      Paul
1           104         5           Paul                      John                      NULL                      John
1           105         6           Ringo                     NULL                      NULL                      NULL

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources