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 primary key columns from db?

Hello all. I need to extract the column names that form the primary key group on a table in SQL Server. I have a table called Account and it contains ten columns. The primary key consists of two columns - MasterAccountID, AccountID. This primary key is a unique constraint and is clustered (it acts as an index as well as a primary key group). I have tried the following to no avail:

exec sp_pkeys Account -> returns no rows

exec sp_helpindex Account -> throws an error stating that the object 'Account' does not exist

If I run the following SQL statement, I can see all of the PK_* constraints in the database, so I know they are there:

select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')

Again, I need to be able to specify a table name and have it return the columns (don't care if it returns extra fields) that make up the primary key fields for that table. Thanks!

Perhaps something like this:

select

*fromsyscolumnswhere id=object_id('yourTable')and colidin(select colidfromsysindexkeyswhere id=object_id('yourTable'))

|||

declare

@.TableNamevarchar(255)

set

@.TableName='tblPersons'

SELECT

CCU.*

FROM

INFORMATION_SCHEMA.Table_Constraints TCJOININFORMATION_SCHEMA.Constraint_Column_Usage CCUON CCU.CONSTRAINT_NAME= TC.CONSTRAINT_NAME

WHERE

CONSTRAINT_TYPE

='PRIMARY KEY'AND TC.TABLE_NAME= @.TableName

but this also works: look at the quotes ''

execsp_pkeys'tblPersons'

How to retrieve Data from SQL Server?

Let us suppose that there are 900 rows in One table, and that table contains neither primary key, nor Identity column.

How can I retrieve all the roows from 201 to 250?(like the middle limits)

In oracle there is a property called RowID, but is there any such item in SQL server?

Nope. All tables should have a primary key. If there isn't one, make one. If you can't make one, then you have a data design issue you need to fix.

How to Re-Structure Form After adding an Identity

I'd created a dataform from a table stored in a SQL database, after that I changed the primary key field to be an identity, refreshed the dataset but when I execute the program, it raises an exception when I try to save the record after adding a new one asking me for the value of this field. What can I do to be sure it will be created automatically?

Move the thread here in order to get more helpful answers.

|||

Hi Juan,

I'm not sure why this got moved here, it seems like a problem with the DataSet definition...

It would help to have the actual error you're getting, but my guess is that the field you added didn't acutally get the Identity proprty set correctly, so it is not generating new values automatically. When you try to add a new record, there is no value specified for your PK so you get the error. Verify that the field works correctly outside of your DataSet, if it doesn't, then you have a problem with the table, if it does, then there is something wrong with your DataSet.

Mike

|||Ok. I've got the answer. After change the datatype in the table as IDENTITY, and refresh the dataset, the identity atribute in the dataset field does not change automatically. Thanks Mike