how to retrieve values from four tables at a time

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

Quote:

Originally Posted by sudhashekhar30

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar|||

Quote:

Originally Posted by sanbala

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.|||

Quote:

Originally Posted by sudhashekhar30

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.

Hi sudha
i coundn't get you.what you trying to asking.
specify clearly...

by
sankar.b
bsankarit@.gmail.com

How to retrieve all information abt all constraints inorder to drop and recreate

Hello,
I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables.
The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively.
Any help or scripts would be appreciated!First step - dropping:

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'|||i first need to store the information about the constraints before dropping them, so that they can be recreated later after the necessary changes have been made..i have 200+ tables...any tips?|||select *
INTO TempConstraintStore
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

INSERT INTO INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM TempConstraintStore

or something like that|||INSERT into system tables...hmmmmmmm

No thanks...

Just go in and Script the contraints and save the script

Do the drops, necessary changes have been made..then rerun the script

What are the changes?

If RI is out of wack the constraints will fail....|||i'm aware that scripting the constraints using EM would make life easier,but no such luck for me..it has to be done using TSQL..i'm using the information schemas to retreive info,like referential_constraints, columns,constraint_column_usage..is that enough??|||try to use SQL-DMO with something like that (VBS):

Set dmoSQLServer = CreateObject("SQLDMO.SQLServer")
dmoSQLServer.Connect "MyServer", "MyLogin", "MyPSWD"

For Each dmoObj In dmoSQLServer.Databases("MyDBName").Tables
If dmoObj.SystemObject = False Then
dmoObj.Script 134348800, dmoObj.Name & ".fky"
End If
Next

dmoSQLServer.Disconnect
Set dmoSQLServer = Nothing

One file storing all FK creates for each table (TableName.fky). If table have no FK this file will be empty|||Similar problem?

I simply want to add a foreign-key constraint to an existing column.

This is what I have but no luck so far.

alter table table_name with check
alter column column_name
add constraint foreign_key_name
references Reference_Table (Reference_column)

I know it can't be far from this - I think I've even done it before!

John|||I'd use something like:ALTER TABLE myTable
ADD CONSTRAINT myConstraint
FOREIGN KEY (myColumn)
REFERENCES anotherTable (differentColumn)-PatP