most view query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

most view query

Author

Topic

new_developer
Starting Member

18 Posts

Posted - 2010-11-16 : 06:38:13

I have three tables, one for documents and has the following columns:
id
title
description
path
Bajis
date
author
And another table to evaluate them has the following columns
id
reting
favorite
view
comment
And another table for the classes has the following columns
id
Description
The relationship between the scale of documents, evaluation and one multi-
Among the agenda items and documents and one multi-
I want to work four queries, show me the document View the most of each classification with the impose a four fixed categories only
i hope it is clear question
and thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-16 : 11:41:31

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

The vague narrative you did post was wrong on so many basic data modeling principles. There is no Universal, Magical "id" in RDBMS that identifiers anything in the Universe. We don't use reserved words for data element names. Etc.

CREATE TABLE Documents
(issn CHAR(10) NOT NULL PRIMARY KEY,
document_title VARCHAR(100) NOT NULL,
document_description VARCHAR(1000) NOT NULL,
file_path VARCHAR(255) NOT NULL,
bajis -- no idea what this is,
publication_date DATE NOT NULL,
author_id CHAR(9) NOT NULL);

The next table has problems. favorite is a value of a rating, not an attribute. I hope you are not using BIT flags in SQL. I assuming that evaluations are collected in a sequence for each publication and that gives us a proper key (and not that silly magical, universal "id" you had).

CREATE TABLE Evaluations
(issn CHAR(10) NOT NULL
REFERENCES Documents (issn), --note DRI
view_seq INTEGER NOT NULL,
PRIMARY KEY (issn, view_seq),
reader_rating INTEGER NOT NULL, --favorite is a rating, not an attribute
reader_comment VARCHAR(1000) NOT NULL);

Please at least given the tables a name. You do not understand how an auxiliary table for an encoding scheme work or how to name the data elements. Again the magical universal "id" shows up.

CREATE TABLE Publication_Classes
(pub_class CHAR(3) NOT NULL PRIMARY KEY, -- Dewey decimal? other standard?
pub_class_description VARCHAR(100) NOT NULL);

But where do you use this classification? It has to be an attribute of a publication, doesn't it?

>> I hope it is clear question <<

No, it is not clear. It looks like the end of your posting was scrambled. There is not even a proper sentence. Read the first two paragraphs of this reply. Please try again, with proper Netiquette.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

new_developer
Starting Member

18 Posts

Posted - 2010-11-18 : 16:36:16

My friend I am sorry to my question that I was not clear
create table categories(
id integer primary key,
name nvarchar(50) not null,
descrription ntext;
)
create table document(
id integer primary key,
title nvarchar(50),
description ntext,
author nvarchar(50),
cid foreign key refernces categories(id);
)
create table reting(
id integer primary key,
reting bit,
favorite bit,
view integer,
comment nvarchar(500);
)
My dear friend, I just want the work to hold up to me the most View the document at each category
thanks And I apologize again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources