One to one Relationship problem - Database Discussion Boards

no all subjects are dynamic

they can be increased or decreased

Sounds like you should do some research on PIVOT and UNPIVOT operators.

Mika

Hi Monu Khan,
ZAK here... ya i do understand your problem... But i strongly suggest you to learn atlest basics of SQL and also have handson training before you come to conclusion that you don't know how....

The question you asked is a bignners levfel one.. This time iam answering you.. But remember "The more you learn... The more you grow..."

Creating View:
**************

CREATE VIEW studentView
AS
	SELECT rollno, english, math, history, total/250 AS NetTotal FROM marksheet

Executing View:
***************

Execute studentView

Try this out monu khan

All the best!!! Cool | :cool:

ZAK

hi. I am developing an IS and I was wondering on how I can load the heavy amount of data in a more efficient way. I was asked to also integrate an algorithm but I don't know where to put it and what to use. The program does much more in SQL queries than C# algorithmic codes. If you know what I mean. To explain it further, if I have about 1 million data entries in my database. How can I possibly load them in a jiffy?

The quickest way to load them is bcp, but I doubt you will load 1 million records 'in a jiffy' what ever method you use Smile | :)

Bob
Ashfield Consultants Ltd

what's BCP? Confused | :confused:

Its a bulk loading utility. Ty BOL or Google. Its the fastest way t get records from a file into a SQL Server database.

Bob
Ashfield Consultants Ltd

hello everybody in this forum

i would like to know what is the appropriate solution for the current case in hand

i have two tables one called userinformation and the other called accountinformation

userinformation
================
id int not null primary key ,
firstname varchar(50) not null,
lastname varchar(50) not null,
email varchar(50) not null

accountinformation
===================
id int not null primary key ,
username varchar(50) not null,
password varchar(50) not null

now there is a relationship between the userinformation table and the accountinformation table (one to one) relationship where id in the userinformation refers to the id in the other table (accountinformation) now there are problems when inserting data into both tables like the following

The INSERT statement conflicted with the FOREIGN KEY constraint "LeftHalfHasMatchingRight". The conflict occurred in database "Sandbox", table "dbo.RightHalf", column 'PairID'.
The statement has been terminated.

now i tried to solve this problem by creating a stored procedure and within that stored procedure i created a transaction in it there is two insert statements like the following

<br />
create procedure adduser (@first varchar(50) , @last varchar(50) , @email varchar(50) , @user varchar(50) ,@pass varchar(50))<br />
as<br />
<br />
begin tran<br />
<br />
insert into userinformation (firstname , lastname , email ) values ('bla','bla','bla')<br />
insert into accountinformation (username , password ) values ('bla','bla')<br />
<br />
<br />
commit<br />
Go<br />

it works just fine but if i tried to insert single record in one of the two tables and try to execute this stored procedure with a new different records for these tables it gives me the same error message because there is a record in the userinformation without referential integrity into the other table which is the accountinformation table .

So please tell me how can i solve this current problem in hand

thank you very much

Human knowledge belongs to the world.

How do you set the primary key value into ID columns? Are they auto-generated.

If you can post table creation scripts (along with constraints), it would help a lot.

Mika

i suggest you the following things!!!

Create Primary Key for "ID" column in userinformation table and make it as IDENTITY,

Crate a new Primary Key column for accountinformation table something like
"accountinformationID" and make it as IDENTITY, Optional but good if you have one.

then Map Primary Key "ID" of userinformation table to "ID" column of accountinformation table, this process is called as Foreign Key creation

Now the basic you need to stick with is that

any insertion on table accountinformation made should have a matching "ID" value from userinformation table to the column "ID" of accountinformation table.

Hope this will help you out!!!! Blush | :O

ZAK

Listen, try this:

userinformation
================
id_user int not null primary key ,<-------
firstname varchar(50) not null, |
lastname varchar(50) not null, |
email varchar(50) not null |
|
accountinformation |
=================== |
id int not null primary key , |
id_user int not null foregn key <---------
username varchar(50) not null,
password varchar(50) not null

allways do this OMG | :OMG:

Heelo All,
I want to arrange a result of query into Row header.
Example :

in one table i have the Names Of Books. I want to arrage each book name as heading in another Table.

Select * from BookNames

BookNames
---------

Java Programming
visual studio.net
Operating Sytems
ORDBMS
System analysis and Design
System Software

I want to arrage the Result as a separate table Like Below

JavaProgarmming visual studio.net OperatingSytems ORDBMS
--------------- ----------------- ----------------- ------

IS it possible? Please Help me.

Thanks
RIZ.......

It is to a certain extent, but what would happen if you had say 1000 booknames? You would not want 1000 columns - and most versions of RDBMS wouldn't allow that many any way.

Bob
Ashfield Consultants Ltd

Sir,
This is my Mini Project I will only use 10 to 15 book's Only. Once i add a book name in One table, it will automatically add as another tables header. Please help me if there is any way?

Thanks
Fathima

take a look at Pivot. Use that with a dynamic sql to construct your book colums.
In this script the @List would bhe your book titles

<br />
BEGIN<br />
	Set @SQL = 'SELECT PortfolioID, Portfolio, ' + @List + char(13) <br />
	Set @SQL = @SQL + 'FROM (SELECT PortfolioID, Portfolio, A.Element, PFClass ' + char(13) <br />
	Set @SQL = @SQL + 'FROM vwPFAttribute A ' + char(13) <br />
	Set @SQL = @SQL + 'INNER JOIN PFTree T ON T.PFClassID = A.PFClassID AND T.TreeID = ' + CONVERT(VARCHAR(20),@TreeID) + char(13) <br />
	Set @SQL = @SQL + 'WHERE A.PortfolioID NOT IN (SELECT PortfolioID FROM dbo.fn_TreeExcludedPortfolios(' + CONVERT(VARCHAR(20),@TreeID) + ')))P' + char(13) <br />
<br />
	Set @SQL = @SQL + 'Pivot (Max(Element) For PFClass In ('  + @List + ')) as Pvt' + char(13) <br />
	Set @SQL = @SQL + 'Order By Portfolio'<br />
<br />
	Print @SQL<br />
END<br />
<br />

Never underestimate the power of human stupidity
RAH

Mycroft Holmes wrote:
look at Pivot.

Seems like alot of questions lately floating around PIVOTs Suspicious | :suss:

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Nasty bloody things until you get the hang of them. Once you get the idea that there are inner and outer queries it starts to fall into place. I used to do a lot of horrible cursor and while selects to get the same result until 2005.

I broke down into 3 steps
1 Build your query to the minimum of data you need (inner query)
2 build your column string = @List
3 wrap these in the pivot query

Never underestimate the power of human stupidity
RAH

Sir,
That query returns a below error in Parse. am using sqlserver 2005.

' Must Declare a Scalar variable @SQL'
' Must declare a Scalar Variable @OList'

What can i Do Now?

This is sample Program only, hardly i will use only 10 to 15 books only. because this is my mini project. Please help me

Astonishing - you actually thought that query would run right, it was there as an example.

rrrriiizz wrote:
What can i Do Now?

Read the other replies, do some research in BOL on pivot queries, find out what dynamic SQL is. You will not be spoon fed with code, we are here to help you increase your skills and LEARN, not do your job for you.

I strongly suggest you get a book in SQL Server and start from there.

Never underestimate the power of human stupidity
RAH

Hi there!!!,

I have one idea for your question.. since this is ur small project try to do it in this way... this is just an alternative way..

Use your front end to for creation of tables...

As you said this may have hardly 15 to 20 records...

Assume that you are using VB.Net as your front end,, then use a for loop to loop through the values underit use table creation script which would greate dynamicly... based on the values.

Hope this gives you a sight!!!

Cheers,
ZAK

ZAK

Thank You So Much, this was working good in my Project. Thanks a lotttttttttt....

You are welcome dude!!!!!

ZAK

we developed windows application using C#,and we created setup file(Using Installer Class also ) for this ,this setup runnig succes fully in my machine ,but problem it was not runnig in Client Machine ,it is showing error " login Failed User is Not Trusted user " . How Can Connect client Sql Server 2005 .Could u Please help me.

From the error message, I presume you're using Windows Authentication to connect to SQL Server. You have two options here,
1) Add the client's login as a trusted user in SQL Server. (This will only work if you're in domain and the client is also a user in the same domain)
2) Use SQL Server Authentication. (Use a username and password to connect to SQL Server)

Thanks.

and also try this one...

http://www.banmanpro.com/support/sql2k.asp[^]

Hope this helps you Wink | ;)

ZAK