How to remove 1:1 Relationships

I have an asset schema problem which I'm not too sure how to resolve...

I have an asset table which stores values common to all asset types. I have created seperate asset type tables(VehicleAssetDetail, PropertyAssetDetail, InvestmentAssetDetail) to store their specific details.

This in turn creates 1:1 relationships between "Asset" table and the respective detail tables.

If I were to store all the details in the "Asset" table it would cause a lot of redundancy bearing in my mind there are about four other asset type details I have excluded from the schema.

Are the 1:1 relationships bad? If they are, how do I resolve them without throwing all the columns from the asset details tables into the Asset table?

Attached is a 15Kb gif image of the schema. I'd really appreciate any help with this... :)
Thanks.A lot of your assertions are fals.

Why would a 1:1 create redundant data?

Only reason I would use a 1:1 is if the row length is too long, then I would catergorize the data into appropriate entities.

But remember, the best join, is none|||Hi Brett...
You may have misunderstood my statement...
If all the columns of each Detail table would be placed in the "Asset" table and the Details tables dropped, most of the columns in the "Asset" table would be null, except for the columns which relate to the specific asset which is inserted.

A 1:1 relationship would not create redundancy.
So back to my original question... :)|||This was raised in another thread which may be worth a read

http://www.dbforums.com/showthread.php?t=1622324

Read the last 3 or 4 posts or so and you'll get the answer ;)

How to refer to report textbox values in another textbox

I want to add up the values in a couple of text boxes in another textbox. How do I refer to the textboxes?

fields!textbox1.value doesn't work..what does?

Hello,

Try this:

ReportItems!textbox1.Value

Hope this helps.

Jarret

How to read return value from sqlpipe send(string) method

Hi guys,
How can i read in my C# client application values returned from sqlpipe send(string)/send(reader method from CLR stored procedure.....following is example of a stored procedure.I want to read datetime string in my C# client
Thanks
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PrintToday()
{
// Put your code here
SqlPipe p;
p = SqlContext.Pipe;
p.Send(System.DateTime.Today.ToString());
}

Hi!

Execute your PrintToday with SqlCommand.ExecuteReader, then read the string from SqlDataReader returned.

|||Exactly how are you supposed to do this? No matter what i do, the reader is always empty.

How to read return value from sqlpipe send(string) method

Hi guys,
How can i read in my C# client application values returned from sqlpipe send(string)/send(reader method from CLR stored procedure.....following is example of a stored procedure.I want to read datetime string in my C# client
Thanks
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PrintToday()
{
// Put your code here
SqlPipe p;
p = SqlContext.Pipe;
p.Send(System.DateTime.Today.ToString());
}

Hi!

Execute your PrintToday with SqlCommand.ExecuteReader, then read the string from SqlDataReader returned.

|||Exactly how are you supposed to do this? No matter what i do, the reader is always empty.

How to Query... Please help

hi friends,

I have a table whose one field is 'monthname' containing month names. I want to get these month names as field values with an SQLQry. no Stored procedures or functions can be used.

Normal Result: (Select monthname Month,Count(ID) Count From TestData Group By Monthname)

MonthCountJan2Feb7Mar3Apr4May5Jun2Jul2Aug4Sept5Oct6Nov4Dec4

My Required Format:

IDJanFebMarAprMayJunJulAugSeptOctNovDec1231234888665555

(this month name is just an example, it can be anything. like year or Flight Name. I mean i cant hard code like 'Select * From TestData where monthName = 'Jan'')

Got me...?

Thanking,

:)

Search these forums for pivot. Or advanced search where I say something about pivot in this forum.

How to query xml data in column type xml

How can I query xml data in column type xml using an xml schema from the same table row in the xmlschema column and return values(not xml) to insert into a seperate table

XML Data Table

C1 =RecordID (int)

C2 =XMLData (xml)

C3 = XMLSchema(xml)

How can I query the XML data using the xmlshema column

and return values(not xml) to insert into table 2

Table 2

C1 = RecordID

C2 = User

C3 = date

thanks

I think you are looking for the XQuery "nodes()" function if I understand you correctly. It sounds like you want to reach into the column that is storing the schema, extract scalar values from that schema and store it in a relational table. If this is what you are trying to do, then the nodes() function should work for you.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

Once you get the data into relational form, you can CURSOR over it and call your INSERT statement with parameters, use INSERT INTO ... SELECT, or generate dynamic SQL.

How to Query to get the Numeric fields Only in the Table

hi Good Day everyone.

how can i query numeric values in the specific fields for example EmployeeID only.

but the field EmployeeID has a datatype of varchar.

for example :

EmployeeID -> Field

001

002

a

b

c

how do i query 001 and 002 only. tnx

here it is,

Code Snippet

Create Table #employee (

[EmployeeID] Varchar(100)

);

Insert Into #employee Values('001');

Insert Into #employee Values('002');

Insert Into #employee Values('a');

Insert Into #employee Values('b');

Insert Into #employee Values('c');

Insert Into #employee Values('1E2');

Insert Into #employee Values('.');

Insert Into #employee Values('$');

Insert Into #employee Values('E0');

Select

*

from

#employee

Where

Isnumeric([EmployeeID]) = 1

And [EmployeeID]not like '%[^0-9]%'

|||Thanks Manivannan.D.Sekaran it helps me a lot.

How to query out these annoying New line characters

Hello, when I imported data from ACCPAC to MS SQL Server 2000, I noticed
there were fields with a SQUARE character appended to the end of the values.
I am guessing these are NEW LINE CHARACTERs.
My question is how can I query these characters out because I want to delete
them. I do not want to go one row at a time and erase it!
Thanx in advance
Jiro Hidaka
Programmer for Medisca
PharmaceutiqueA square is unlikely to be a new line character.
Can you figure out what the ascii value is?
SELECT ASCII(SUBSTRING(col, position_where_square_is, 1));
--UPDATE tbl SET col = REPLACE(col, CHAR(result_from_above));
"Jiro" <medisca@.newsgroups.nospam> wrote in message
news:F558657A-D1DB-4083-9C0B-11F01244F366@.microsoft.com...
> Hello, when I imported data from ACCPAC to MS SQL Server 2000, I noticed
> there were fields with a SQUARE character appended to the end of the
> values.
> I am guessing these are NEW LINE CHARACTERs.
> My question is how can I query these characters out because I want to
> delete
> them. I do not want to go one row at a time and erase it!
> Thanx in advance
> --
> Jiro Hidaka
> Programmer for Medisca
> Pharmaceutique|||What are you looking for - carriage return characters, line feed characters
or both?
char(10) = line feed
char(13) = carriage return
declare @.str varchar(256)
set @.str ='this is
a new line'
select replace(@.str, char(10) + char(13), '')
ML
http://milambda.blogspot.com/|||> select replace(@.str, char(10) + char(13), '')
Actually, most times that pair will be inserted in the opposite order, 13
first, then 10.|||Yes, of course! I was a bit fast with copy/paste.
Thanks for pointing it out!
ML
http://milambda.blogspot.com/

How to Query for NULL in a database

In my database, in a few tables there are NULL values in some of the columns. This is okay, but I need to know how to query for nulls. For example I tried the following query but it did not work:

select*from Employeewhere DateOfBirth=NULL

This did not work so I also tried the following:

select*from Employeewhere DateOfBirth='NULL'

Neither of these worked. Can someone help me out?

NULL means unknown. So 2 NULLs are not equal. Read up books online about NULLs. Its an important topic. For your query, you would need to write it as:

SELECT * FROM Employee WHERE DateOfBirthIS NULL