rows repeating themselves????

[Dean]

hi there

can anybody tell me why sometimes when you create a veiw in sql 2000 and run it every row in the results is shown twice?

and if so is there a simply way to stop it?

as it can be rather annoying.:mad: :confused:

[Dean]

here is the sql statement that is producing the results that produce two of each recod in the database.

can any one spot what where i am going wrong this is what the sql2000 enterprise manager is generating when i ask for only records with yes in the posted_onsite collumn and for records with secure logistices in the secure_logistics collumn.

any help appreciated?

SELECT TOP 100 PERCENT ArticleID, to_be_posted, Posted_on_site, Secure_logistics, Headline, Leadin, Fullstory, Keywords, Source, source_info,
Source_logo, Source_author, Source_date, Source_Type, Submitted_by_name, Submitted_on, FullstoryLink, Printversionlink, importance, Layout,
Source_url, First_source_url, Secound_souce_url, Third_source_url
FROM dbo.Articles
WHERE (Secure_logistics = N'Secure Logistics') AND (Posted_on_site = N'yes')
ORDER BY ArticleID DESC

[mat]

So you are saying that on the actual page (php, asp or whatever) you are getting 2 of each row. Duplicates ?

'SELECT DISTINCT' or 'GROUP BY'?

"when you create a veiw" -what is that ? 'view'



:)

[Dean]

It turned out after a bit of investigating the sql server had made a bit of a error.

when i imported the original database up from access 2000 it had took the 750 odd rows of data then numbered the rows:
1
1
2
2
3
3
4
4
5
5
6
6
7
7

So i had two different rows with the same number and what looked like duplicate rows where in fact different for half the rows but the other half where dupilcate rows. I noticed it after i realised there where 1400 rows on the main table it's self and it was not the just in the views.

So i had to start again this time i corrected a small error in the access database that had not affected it working in access but did in sql. So now everything is running sweetly.


Cheers for the help anyway:thumbsup: :thumbsup:

[mat]

Good to hear. :) What was the error in access?

[Dean]

I have not got a clue.

I did a repair of the tool bar options and it came back saying it had fixed error something or other but i had not even realised it was there.

The database had been running a large news centre for the last year problem free.

But the one thing i did notice where data had been copied and pasted in from word their was a lot of squares and white space that shouldn't have been there once i was going through the data in the sql manager after i managed toget everything working properly. Once i hadtrwaled through everything and removed them everything seemed to go a little fast and look like it did in the access version.

But then thats micrososft for you.

:confused: :confused: :confused:

It works now and for now that will do me.

[mat]

Hmmm, oh well :cool:

[Kiwi]

Originally posted by mat
what is that ? 'view'
A view is a virtual table. It is a stored query, usually based on two or more tables. It can be used as a table by other queries (by default, all queries created in Access are, in fact, views).

The main advantages are efficiency and security. You can merge complex tables that store complete information to produce more simple tables that contain live information without any data duplication. You can also give the view query different access, meaning that you can let people see only some columns of a table (eg a view restrict who can see creditcard numbers, without restricting who can see other customer details).

They are not supported in mySql.

[mat]

Thanks for the info.

So for instance -I could create a view by storing a query like:

SELECT things FROM tables Where thing1 = thing2;

and the results of this query (which i guess make up the table) would be a like a view ? I could then query this view and do other wierd things with it.

[Dean]

on one of my sites I have six sections in the news centre with archives and i am running 20 or so stored views(queries) these match data from 4 tables to produce what is displayed on the screen.

now one table has a column with a date in that the some of the other colums use as the criteare for the queries.

so if table 1 row on said the 3/3/02 then all the queries use this as the criteare for the results.

here is an sql example of one of the views i am using to produce a list of dates of entries in the archives.

SELECT TOP 100 PERCENT dbo.news_archive_articles.news, COUNT(dbo.news_archive_articles.to_be_posted) AS No_of_Articles,
dbo.news_archive_articles.to_be_posted
FROM dbo.news_archive_articles INNER JOIN
dbo.DISPLAY ON dbo.news_archive_articles.to_be_posted < dbo.DISPLAY.POSTED_ON AND
dbo.news_archive_articles.to_be_posted < dbo.DISPLAY.POSTED_ON
GROUP BY dbo.news_archive_articles.news, dbo.news_archive_articles.to_be_posted
HAVING (COUNT(dbo.news_archive_articles.to_be_posted) > 1)
ORDER BY dbo.news_archive_articles.to_be_posted DESC

this produces a result like so

1/12/02
1/11/02
1/5/02
5/4/02
4/4/02

this veiw produces results older than the date in this colum.
this is simply use the a filed out of one table to decide the criteare for the results of what is produced in another veiw.

hope this helps:thumbsup:

[Kiwi]

Something like that. A live example might be something like:
CREATE VIEW ABview
AS SELECT
A.Name,
A.Age,
B.Height,
B.Width
FROM
Atable as A,
Btable as B
WHERE
A.Active="T" AND
A.Key=B.Akey AND
B.Current="F"You can then run a query on the view:SELECT
*
FROM ABview AS A
WHERE
A.Name LIKE "%Bob%"This returns all active user's current height and width. If you also happen to store other information in the tables (eg home address), these will not be available to anyone who can see the view but not the tables.

They are really important for designing a database application, because they allow you to seperate the administrative data from the functional data at the user level, without compromising the data available at the admin level.

It might be worth taking this to a new thread -- I don't want to distract from Dean's question.

[mat]

No need, Thanks both ;)

[dhtmlhelp]

Hi everyone,

what is the alternative to 'view' in mySQL/PHP then ...

thanks,

DH

[Kiwi]

what is the alternative to 'view' in mySQL/PHP then ...
See here. (http://www.codingforums.com/showthread.php?s=&threadid=13191)