mapping two references from one table ?

Hi, I'm new to SQL programming and SQL 2000 server.
My problem is that I have a table that contains antenna information.
(Their can be different types of antenna's)
I have another table that points to the antenna table twice, because it
needs two different types of antennas. IE: GPS and radio
When I try to map the results, I don't know how to specify which one I
want to reference.
For example: This is what the query builder builds for me
SELECT DrtType.TxAntennaId, DrtType.GpsAntennaId, Antenna.AntennaId,
Antenna.AntennaName FROM DrtType INNER JOIN Antenna ON
DrtType.TxAntennaId = Antenna.AntennaId AND DrtType.GpsAntennaId =
Antenna.AntennaId
When I try to format the datagrid. I need to map each value that I use
individually
//// Add GPS Antenna
DataGridColumnStyle TextCol_3 = new DataGridTextBoxColumn();
TextCol_3.MappingName = "AntennaName";
TextCol_3.HeaderText = "Gps Antenna";
TextCol_3.Width = defaultWidth;
dgStyle.GridColumnStyles.Add(TextCol_3);
But I only have one AntennaName to map.
Can I do something like:
DrtType.TxAntennaId.AntennaName
DrtType.GpsAntennaId.AntennaName
Thank you for you helpOn 29 Nov 2005 18:11:07 -0800, oracle wrote:

>Hi, I'm new to SQL programming and SQL 2000 server.
>My problem is that I have a table that contains antenna information.
>(Their can be different types of antenna's)
>I have another table that points to the antenna table twice, because it
>needs two different types of antennas. IE: GPS and radio
>When I try to map the results, I don't know how to specify which one I
>want to reference.
>For example: This is what the query builder builds for me
>SELECT DrtType.TxAntennaId, DrtType.GpsAntennaId, Antenna.AntennaId,
>Antenna.AntennaName FROM DrtType INNER JOIN Antenna ON
>DrtType.TxAntennaId = Antenna.AntennaId AND DrtType.GpsAntennaId =
>Antenna.AntennaId
Hi oracle,
You have to join the Antenna table twice, and use aliases to
distinguishe between the two:
SELECT D.TxAntennaId, D.GpsAntennaId,
TX.AntennaName AS TX_Antenna,
GPS.AntennaName AS GPS_Antenna
FROM DrtType AS D
INNER JOIN Antenna AS TX
ON TX.AntennaId = D.TxAntennaId
INNER JOIN Antenna AS GPS
ON GPS = D.GpsAntennaId
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Many-to-Many Variation

In establishing a many-to-many relationship between two tables, a 3rd
"junction" table is created that contains at least two foreign keys.
No problem.
But - what is a recommended way to proceed to create a many-to-many
relationship between (1) one table, and (2) two other tables.
For example, say you are designing a database for a school and you are
establishing a many-to-many relationship between [Classes] and [Students]
and [Teachers].
There would obviously be a Classes table. A "junction table" could then have
foreign keys to Classes and Students. That would get us a many-to-many
relationship between Classes and Students. But we also need to show that
relationship between Classes and Teachers.
What do we do with the "junction table"? Do we add a 3rd foreign key to the
existing table? Do we create another junction table altogether?
I'd appreciate some guidance on this.
Thanks!"Smithers" <A@.B.COM> wrote in message
news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> In establishing a many-to-many relationship between two tables, a 3rd
> "junction" table is created that contains at least two foreign keys.
> No problem.
> But - what is a recommended way to proceed to create a many-to-many
> relationship between (1) one table, and (2) two other tables.
> For example, say you are designing a database for a school and you are
> establishing a many-to-many relationship between [Classes] and [Students]
> and [Teachers].
> There would obviously be a Classes table. A "junction table" could then
> have foreign keys to Classes and Students. That would get us a
> many-to-many relationship between Classes and Students. But we also need
> to show that relationship between Classes and Teachers.
> What do we do with the "junction table"? Do we add a 3rd foreign key to
> the existing table? Do we create another junction table altogether?
>
Assuming that a class has many teachers, you would use seperate junction
table. Each junction table models a different relation. One models the
"student is a member of class" relation, and the other models "class is
taught by teacher" relation.
If each class has a single teacher, then add a foreign key on the Classes
table referencing Teachers.
David|||Google up "Fifth Normal Form (5NF)"|||And then if you want to model the relationship between Teachers and Students
...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OUsBopVlGHA.2128@.TK2MSFTNGP04.phx.gbl...
> "Smithers" <A@.B.COM> wrote in message
> news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> Assuming that a class has many teachers, you would use seperate junction
> table. Each junction table models a different relation. One models the
> "student is a member of class" relation, and the other models "class is
> taught by teacher" relation.
> If each class has a single teacher, then add a foreign key on the Classes
> table referencing Teachers.
> David|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
> And then if you want to model the relationship between Teachers and
> Students ...
>
That information can be derived from the existing relations:
Select Class.Teacher, StudentClass.Student
from Class
join StudentClass
on Class.ID = StudentClass.ID
David|||I disagree.
But my disagreement is positioned on having had to work out this kind of
issue before. (I may be overlooking some simple solution you have worked
out. )
Student A has a Class B
StudentClass:
StudentClass_ID = 1, Student=A, Class=B
Teacher ZZ teaches Class B, Teacher YY also teaches Class B.
Class:
Class_ID=1,Class=B, Teacher=YY
Class_ID=2,Class=B, Teacher=ZZ
Student A's Class B is taught by Teacher ZZ
Your query will tell me that Student A has a relationship with Teacher YY
which is NOT true.
More has to be done to develop a relationship between Teachers and Students.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:elGU93WlGHA.2392@.TK2MSFTNGP04.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
>
> That information can be derived from the existing relations:
> Select Class.Teacher, StudentClass.Student
> from Class
> join StudentClass
> on Class.ID = StudentClass.ID
> David
>
>|||Arnie Rowland wrote:
> I disagree.
> More has to be done to develop a relationship between Teachers and Student
s.
> --
Correct. This problem is solved by 5th Normal Form.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't know if I am good enough to interefere in the clash of the titans.
But Can't we have something like this?
Take it simply.. A student is associated to a particular class by a
particular teacher.
And there is no explicit teacher student relation.
The same example you had quoted.
Student A has a Class B
StudentClass:
StudentClass_ID = 1, Student=A, Class=B
Teacher ZZ teaches Class B, Teacher YY also teaches Class B.
Class:
Class_ID=1,Class=B, Teacher=YY
Class_ID=2,Class=B, Teacher=ZZ
If Student A's Class B is taught by Teacher ZZ
The the student class table should look like this
StudentClass:
StudentClass_ID = 1, Student=A, Class_ID=2
If a student is attending a session, it needs a class and a teacher.
and if such a class is happening then we should be having an entry in the
class table. Am I missing something?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi David (and all in the discussion),
To tell the truth, I haven't really bothered about 5NF till now.Assumed
anything above 3rd was not practical :)
But the post yesterday and the replies intrigues me. I had checked up on a
few websites and the examples that is given there doesn't convince me. Can
you direct me to a website where the explaination is given. And If I am not
able to understand, then I can discuss with reference to that.
Thanks a lot.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"David Portas" wrote:

> Arnie Rowland wrote:
> Correct. This problem is solved by 5th Normal Form.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

many DISTINCT queries

Hi,

I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.

Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:

select distinct CATEGORY from table TEST

Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.

I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.

I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.

Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?

Thanks for any hints!"Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> Hi,
> I have a table that contains log data, usually around a million records.
The
> table has about 10 columns with various attributes of the logged data,
> nothing special. We're using SQL Server 2000.
> Some of the columns (for example "category") have duplicate values
> throughout the records. We have a web page that queries the table to show
> all the unique columns, for example:
> select distinct CATEGORY from table TEST
> Obviously the server has to scan all rows in order to get all unique
columns
> which takes quite a while, especially since that web page contains several
> of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
that
> also add to the load.
> I already created indexes on the CATEGORY (actually on all categories)
> column which might help a little but I'm pretty sure that there has got to
> be a better way.
> I also create a view (select distinct CATEGORY from table TEST) and tried
to
> index it, but it won't let me index a query that contains a DISTINCT
> statement.
> Isn't there a way to create an index that contains only the distinct
values?
> Is there another way to speed this up?
>
> Thanks for any hints!

If only you load/update the data relatively infrequently, then you could
create a 'lookup' table for each attribute, and populate them from the main
table after loading it (rather like the dimensions in a star schema):

insert into dbo.Categories (Category)
select distinct Category
from dbo.Test

Your client code could then query the lookup tables instead of the log
table. If you want to use indexed views, then you could create a view like
this:

create view dbo.Categories
with schemabinding
as
select Category, count_big(*) as 'Occurrences'
from dbo.Test
group by Category

That should be indexable, although there are quite a few other restrictions,
so you would need to check them out. But having multiple indexed views on
the table would make data modifications much slower, so if the data changes
frequently you might have to use some sort of lookup table approach anyway.

Simon|||> select distinct CATEGORY from table TEST
Try experimenting with group by instead of distinct. Also in query
analyzer, enable view execution plan, to get an idea what mssql is
doing. "select category from mytable group by category"|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4023d837$1_1@.news.bluewin.ch...
> "Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
> news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> > Hi,
> > I have a table that contains log data, usually around a million records.
> The
> > table has about 10 columns with various attributes of the logged data,
> > nothing special. We're using SQL Server 2000.
> > Some of the columns (for example "category") have duplicate values
> > throughout the records. We have a web page that queries the table to
show
> > all the unique columns, for example:
> > select distinct CATEGORY from table TEST
> > Obviously the server has to scan all rows in order to get all unique
> columns
> > which takes quite a while, especially since that web page contains
several
> > of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
> that
> > also add to the load.
> > I already created indexes on the CATEGORY (actually on all categories)
> > column which might help a little but I'm pretty sure that there has got
to
> > be a better way.
> > I also create a view (select distinct CATEGORY from table TEST) and
tried
> to
> > index it, but it won't let me index a query that contains a DISTINCT
> > statement.
> > Isn't there a way to create an index that contains only the distinct
> values?
> > Is there another way to speed this up?
> > Thanks for any hints!
> If only you load/update the data relatively infrequently, then you could
> create a 'lookup' table for each attribute, and populate them from the
main
> table after loading it (rather like the dimensions in a star schema):
> insert into dbo.Categories (Category)
> select distinct Category
> from dbo.Test
> Your client code could then query the lookup tables instead of the log
> table. If you want to use indexed views, then you could create a view like
> this:
> create view dbo.Categories
> with schemabinding
> as
> select Category, count_big(*) as 'Occurrences'
> from dbo.Test
> group by Category
> That should be indexable, although there are quite a few other
restrictions,
> so you would need to check them out. But having multiple indexed views on
> the table would make data modifications much slower, so if the data
changes
> frequently you might have to use some sort of lookup table approach
anyway.

Thanks, I tried the indexed view and that seems to work OK now, pretty
fast - can't complain. Data shouldn't be updated that often so that should
be OK. Otherwise I might have to go with a lookup table - but it's not a
real good solution for our scenario for reasons I'm not going to bore
anybody with :)

Thanks!|||"louis nguyen" <louisducnguyen@.hotmail.com> wrote in message
news:b0e9d53.0402061216.5d4f6e56@.posting.google.co m...
> > select distinct CATEGORY from table TEST
> Try experimenting with group by instead of distinct. Also in query
> analyzer, enable view execution plan, to get an idea what mssql is
> doing. "select category from mytable group by category"

Yes, the group thing worked great - I also analyzed the execution plan and
now it's only returning the actual number of rows I'm getting - not the
whole table anymore.

Thanks.

Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara

Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara