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?