Sybase Database - How to combine two unrelated tables into a single table

Author

How to combine two unrelated tables into a single table

2005-08-04, 7:23 am

Dear all

How to merge two unrelated tables into a single table? They have same
id number for relationship. But,I can't use the join function with id
numbers. It is because some data can't be extracted from the database
by using id numbers as a condition.

I am use Sybase server. The table is written in view

My situation is:
Table A
studentid
firstname
lastname
age

Table B
studentid
course_name
school_address
school_campus
school_phone

Wanted result table
customerid firstname lastname age course_name school_address
school_campus school_phone <-- they are column headers(in the same row)

how can I write the SQL query? what functions I should use?

Thanks

ZeldorBlat

2005-08-04, 8:23 pm

I'm not sure what you mean when you say "I can't use the join function
with id
numbers. It is because some data can't be extracted from the database
by using id numbers as a condition."

Without joining the two tables, how would you go about combining them?
(hint: you can't).

hatfieldrc@gmail.com

2005-08-05, 11:23 am

Perhaps the student_id is actually the SSN or similar that can't be
published in a report? If so, you can still use the join to get your
data, just don't include it in the results table. I see that you've
already used a new column "customerid" - if this is established as an
identity column you shouldn't have a problem.

create table result_table (
customerid numeric(8,0) identit
y,
firstname varchar(30) null,
lastname varchar(30) null,
age smallint null,
course_name varchar(60) not null,
school_address varch
ar(60) not null,
school_campus varcha
r(60) not null,
school_phone varchar
(30) null )
lock allpages
on "default"
go

insert result_table (
firstname, lastname,
age,
course_name, school_
address, school_campus,
school_phone)
select firstname, lastname,
age,
course_name, school_
address, school_campus,
school_phone
from table_A a,
table_B b
where a.studentid = b.studentid

HTH,
Chris Hatfield
Falls Church, VA
US of A

Sponsored Links