Need some advice. Thank You.

Hello,
Today I am creating my first one-to-many relationship database.
My main table is:
USERS
Then I have 4 tables related with this one:
PAYMENTS, ORDERS, BOOKS, ARTICLES
For each user I need to create a field named VALUE.
VALUE = N(PAYMENTS)*4 + N(ORDERS)*2 + N(BOOKS)*10 + N(ARTICLES)*5
N = Number of... / Example: N(PAYMENTS) means "Number of Payments".
My questions are:
1. Should I place the value field in USERS table or create a table named
VALUES and have it connected to USERS table?
2. How can I keep my VALUE field updated for each USER?
Thank You Very Much,
Miguel> 1. Should I place the value field in USERS table or create a table
named
> VALUES and have it connected to USERS table?
None of the above. Don't store calculated values in the database. Put
the calcs in your views, queries and procs.

> 2. How can I keep my VALUE field updated for each USER?
Not a problem if you don't store the value.
If you need more help then the following article explains the best way
to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> 1. Should I place the value field in USERS table or create a table named
Consider using a view instead. Deriving calculated values are generally
better than having them as persisted data in any base table.
This is not an issue, if a view is used. For details, see the topic on
views, creating a view and if required for any performance reasons, indexed
views, in SQL Server Books Online.
Anith

Need More SQL Query NewID Help

Excuse my ignorance because I don't do advance db related programming,
but have no other choice at the moment. My experience is limited to
simple queries.

I need to have the following query display the recordset in random order
based on RecordID (unique key) if possible. I tried the ORDER BY NewID()
at the end and it generated an error (ORDER BY items must appear in the
select list if SELECT DISTINCT is specified.) I guess because of the sub
query. I would also like for the recordset to display a different 10
records on each hit to the page, not just the same 10 records in random
order. I wasn't sure if the SELECT commands I have in place are
sufficient for this task.

Thanks in advance for any assistance.

SELECT DISTINCT
TOP 10 dbo.ShowcaseRides.RecordID,
dbo.ShowcaseRides.CustomerID, dbo.ShowcaseRides.PhotoLibID,
dbo.ShowcaseRides.Year,
dbo.ShowcaseRides.MakeShowcase,
dbo.ShowcaseRides.ModelShowcase, dbo.ShowcaseRides.VehicleTitle,
dbo.ShowcaseRides.NickName,
dbo.ShowcaseRides.SiteURL,
dbo.ShowcaseRides.ShowcaseRating, dbo.ShowcaseRides.ShowcaseRatingImage,
dbo.ShowcaseRides.ReviewDate,
dbo.ShowcaseRides.Home,
dbo.ShowcaseRides.EntryDate, dbo.Customers.UserName,
dbo.Customers.ShipCity, dbo.Customers.ShipRegion,
dbo.Customers.ShipPostalCode,
dbo.Customers.ShipCountry, dbo.Customers.LastName,
dbo.Customers.FirstName, dbo.Customers.MemberSince,
dbo.ShowcaseRides.Live,
dbo.ShowcaseRides.MemberLive, dbo.Accessories.Make,
dbo.Accessories.Model, Photos.Path
FROM dbo.ShowcaseRides INNER JOIN
dbo.Customers ON dbo.ShowcaseRides.CustomerID =
dbo.Customers.CustomerID INNER JOIN
dbo.Accessories ON dbo.ShowcaseRides.MakeShowcase
= dbo.Accessories.MakeShowcase AND
dbo.ShowcaseRides.ModelShowcase =
dbo.Accessories.ModelShowcase INNER JOIN
(SELECT MIN(dbo.ShowcasePhotos.PhotoPath)
AS Path, RecordID
FROM dbo.ShowcasePhotos
GROUP BY RecordID) Photos ON
dbo.ShowcaseRides.RecordID = Photos.RecordID INNER JOIN
dbo.ShowcasePhotos ON Photos.Path =
dbo.ShowcasePhotos.PhotoPath
WHERE (dbo.ShowcaseRides.MemberLive = 1) AND (dbo.ShowcaseRides.Live
= 1) AND (dbo.ShowcaseRides.MakeShowcase = @.MMColParam)
ORDER BY dbo.ShowcaseRides.EntryDate DESC

Regards,

Darin L. Miller
Paradyse Development
~-~-~-~-~-~-~-~-~-~-~-~-~-~-
"Some things are true whether you believe them or not." - Nicolas Cage
in City of AngelsParadyse (support@.paradysed.com) writes:

Quote:

Originally Posted by

Excuse my ignorance because I don't do advance db related programming,
but have no other choice at the moment. My experience is limited to
simple queries.
>
I need to have the following query display the recordset in random order
based on RecordID (unique key) if possible. I tried the ORDER BY NewID()
at the end and it generated an error (ORDER BY items must appear in the
select list if SELECT DISTINCT is specified.) I guess because of the sub
query. I would also like for the recordset to display a different 10
records on each hit to the page, not just the same 10 records in random
order. I wasn't sure if the SELECT commands I have in place are
sufficient for this task.

Hugo has already asked why you have the DISTINCT there. Have you tried
simply to remove it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I removed the DISTINCT and it showed the same record 10 times. Who is
Hugo?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns97FA1AC01EC0Yazorman@.127.0.0.1:

Quote:

Originally Posted by

Paradyse (support@.paradysed.com) writes:

Quote:

Originally Posted by

Excuse my ignorance because I don't do advance db related programming,
but have no other choice at the moment. My experience is limited to
simple queries.

I need to have the following query display the recordset in random order
based on RecordID (unique key) if possible. I tried the ORDER BY NewID()
at the end and it generated an error (ORDER BY items must appear in the
select list if SELECT DISTINCT is specified.) I guess because of the sub
query. I would also like for the recordset to display a different 10
records on each hit to the page, not just the same 10 records in random
order. I wasn't sure if the SELECT commands I have in place are
sufficient for this task.

>
Hugo has already asked why you have the DISTINCT there. Have you tried
simply to remove it?
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Paradyse (support@.paradysed.com) writes:

Quote:

Originally Posted by

I removed the DISTINCT and it showed the same record 10 times.

Looks like you have to refine the query to weed out the duplicates then.
Probably you have an in sufficient join condition somewhere.

Without knowledge of the tables, and how they are related, it's difficult
to assist. At a very minimum we would need to see the table definition
including keys. You can script table definitions from Enterprise Manager
or SQL Server Managerment Studio, whichever you are using. Make us a
favour and remove [] and COLLATE clauses before you post it.

You could also try to cut down the query and removing tables until you
no longer get the duplicates. The point would be to track down how the
duplicates are introduced, and then you can work from there.

Quote:

Originally Posted by

Who is Hugo?

Hugo is Hugo Kornelis, another SQL Server MVP. I'm fairly sure that he
responded to one of your earlier posts.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need Help!

Does anyone know if there are dependencies to a table,
after the table is modified, the dependencies don't show
up in the list however all related procedures, triggers,
etc. still exist in the database.
Will this have any impact?
Thank you,
LindaHi,
execute the below command and see dependencies are there
sp_depends <table_name>
This command will not display the objects dependant on other databases.
Note:
For safe execute SP_RECOMPILE <table_name>
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1dcd201c45482$6d8a0520$a101280a@.phx
.gbl...
> Does anyone know if there are dependencies to a table,
> after the table is modified, the dependencies don't show
> up in the list however all related procedures, triggers,
> etc. still exist in the database.
> Will this have any impact?
> Thank you,
> Linda|||Hari,
I saw about 61 dependecies for this one table.
After I added columns to this table, dependencies are
gone 'cause when I run sp_depends I don't see
those dependencies. Even the total no. of record
in sp_depends is minus 61. Is there any way I
can avoid this? How do I restore all dependencies?
All tables, procedures, etc. are there.
How do I link them?
I ran sp_stored_procedures to confirm all procs are
there.
Thanks for your help!
-Linda

>--Original Message--
>Hi,
>execute the below command and see dependencies are there
>sp_depends <table_name>
>This command will not display the objects dependant on
other databases.
>Note:
>For safe execute SP_RECOMPILE <table_name>
>Thanks
>Hari
>MCDBA
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1dcd201c45482$6d8a0520$a101280a@.phx
.gbl...
show[vbcol=seagreen]
triggers,[vbcol=seagreen]
>
>.
>

Need Help!

Does anyone know if there are dependencies to a table,
after the table is modified, the dependencies don't show
up in the list however all related procedures, triggers,
etc. still exist in the database.
Will this have any impact?
Thank you,
Linda
Hi,
execute the below command and see dependencies are there
sp_depends <table_name>
This command will not display the objects dependant on other databases.
Note:
For safe execute SP_RECOMPILE <table_name>
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1dcd201c45482$6d8a0520$a101280a@.phx.gbl...
> Does anyone know if there are dependencies to a table,
> after the table is modified, the dependencies don't show
> up in the list however all related procedures, triggers,
> etc. still exist in the database.
> Will this have any impact?
> Thank you,
> Linda
|||Hari,
I saw about 61 dependecies for this one table.
After I added columns to this table, dependencies are
gone 'cause when I run sp_depends I don't see
those dependencies. Even the total no. of record
in sp_depends is minus 61. Is there any way I
can avoid this? How do I restore all dependencies?
All tables, procedures, etc. are there.
How do I link them?
I ran sp_stored_procedures to confirm all procs are
there.
Thanks for your help!
-Linda

>--Original Message--
>Hi,
>execute the below command and see dependencies are there
>sp_depends <table_name>
>This command will not display the objects dependant on
other databases.
>Note:
>For safe execute SP_RECOMPILE <table_name>
>Thanks
>Hari
>MCDBA
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1dcd201c45482$6d8a0520$a101280a@.phx.gbl...
show[vbcol=seagreen]
triggers,
>
>.
>