sql add or delete repeat row

Question

I have an output like this:

id  name    date        school  school1
1   john    11/11/2001  nyu ucla
1   john    11/11/2001  ucla    nyu
2   paul    11/11/2011  uft mit
2   paul    11/11/2011  mit uft

i will like to achieve this:

id  name    date        school  school1
1   john    11/11/2001  nyu ucla
2   paul    11/11/2011  mit uft

I am using direct join as in

select distinct
  a.id, a.name,
  b.date,
  c.school

  a1.id, a1.name,
  b1.date,
  c1.school

from table a, table b, table c,table a1, table b1, table c1

where
a.id=b.id
and...

etc, etc

any ideas? thanks

Answer 1

We will need more information such as what your tables contain and what you are after. One thing I noticed is you have a school and then school1. 3nf states that you should never duplicate fields and append numbers to them to get more information even if you think that the relationship will only be 1 or 2 additional items. You need to create a second table that stores a user associated with 1 to many schools.

Answer 2

I agree with everyone else that both your source table and your desired output are poor design. While you probably can't do anything about your source table, I recommend the following code and output:

Select id, name, date, school from MyTable;
union
Select id, name, date, school1 from MyTable;
(repeat as necessary)

This will give you results in the format:

id  name    date        school
1   john    11/11/2001  nyu
1   john    11/11/2001  ucla
2   paul    11/11/2011  mit
2   paul    11/11/2011  uft

(Note: in my version of SQL, union queries automatically select distinct records so the distinct flag isn't needed) With this format, you could easily count the number of schools per student, number of students per school, etc.

If processing time and/or storage space is a factor here, you could then split this into 2 tables, 1 with the id,name & date, the other with the id & school (basically what JonH just said). But if you're just working up some simple statistics, this should suffice.

Answer 3

This problem was just too irresistable, so I just took a guess at the data structures that we are dealing with. The technology wasn't specified in the question. This is in Transact-SQL.

create table student
(
    id int not null primary key identity,
    name nvarchar(100) not null default '',
    graduation_date date not null default getdate(),
)
go

create table school
(
    id int not null primary key identity,
    name nvarchar(100) not null default ''
)
go

create table student_school_asc
(
    student_id int not null foreign key references student (id),
    school_id int not null foreign key references school (id),
primary key (student_id, school_id)
)
go

insert into student (name, graduation_date) values ('john', '2001-11-11')
insert into student (name, graduation_date) values ('paul', '2011-11-11')
insert into school (name) values ('nyu')
insert into school (name) values ('ucla')
insert into school (name) values ('uft')
insert into school (name) values ('mit')
insert into student_school_asc (student_id, school_id) values (1,1)
insert into student_school_asc (student_id, school_id) values (1,2)
insert into student_school_asc (student_id, school_id) values (2,3)
insert into student_school_asc (student_id, school_id) values (2,4)



select
    s.id,
    s.name,
    s.graduation_date as [date],
    (select max(name) from 
        (select name, 
                RANK() over (order by name) as rank_num 
         from school sc
         inner join student_school_asc ssa on ssa.school_id = sc.id
         where ssa.student_id = s.id) s1 where s1.rank_num = 1) as school,
    (select max(name) from 
        (select name, 
                RANK() over (order by name) as rank_num 
         from school sc
         inner join student_school_asc ssa on ssa.school_id = sc.id
         where ssa.student_id = s.id) s2 where s2.rank_num = 2) as school1

from
    student s

Result:

id  name  date       school  school1 
--- ----- ---------- ------- --------
1   john  2001-11-11 nyu     ucla
2   paul  2011-11-11 mit     uft