ID Cards with SQL 2005

[hayjo86]

Hi Peeps,

Im working on creating ID cards for the sixth form college i work for.

I have a table holding the student data and a table with staff data. I have created a view for the student table and wish to do so for the staff one, then once i have the 2 views i can create a generic view which the id card will use for populating its data, (name, photo, barcode...etc), but one thing has entered my mind - if the field names for staff and students are different how can i create a generic view.

example:

studentID and teacherCode are unique for the person, but i want to make a view that will put both these in a field called MemberID for instance...

im not very clued up with SQL 2005 so any help is much appreciated.

[rpwillis]

Hi,

You can do this fairly simply by aliasing the columns. If they have different incompatible datatypes you may need to cast one to the other.

A sample script is

create table pupil (studentid int, name varchar(10))
go

create table teacher (staffcode char(2), name varchar(10))
go

insert into pupil(studentid, name) values(1, 'fred')
insert into pupil(studentid, name) values(2, 'sam')
insert into teacher(staffcode, name) values('AA', 'bob')
go

create view members as
select cast(studentid as varchar) as memberid, name from pupil
union
select staffcode as memberid, name from teacher
go

select * from members
go

drop view members
drop table pupil
drop table teacher
go

Richard

[hayjo86]

I tried wat u sed, but i get a message saying:

'CREATE VIEW' must be the first statement in a query batch.

What does this mean?