SEO

#1 (**permalink**) 04-08-2008, 06:00 PM

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

#2 (**permalink**) 04-08-2008, 06:00 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).

#3 (**permalink**) 04-08-2008, 06:01 PM

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) identity,
firstname varchar(30) null,
lastname varchar(30) null,
age smallint null,
course_name varchar(60) not null,
school_address varchar(60) not null,
school_campus varchar(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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to combine two unrelated tables into a single table