Random problem - 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 2005 Forums

Other SQL Server Topics (2005)

Random problem

Author

Topic

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 06:45:54

Hope this is placed rigth

I have this movie database where I want to take a random movie out on the frontpage with Name, Genre and Description. But I have done something wrong in my sql and can't see what it could be.
It writes random moviename and random moviegenre out and there should just came a random moviename
Maybe someone her can se what I has done wrong

SELECT TOP (1)
Movie.MovieID, Movie.MovieNavn,Movie.GenreID, Movie.MovieGenreID,
Genre.GenreNavn, MovieGenreRelation.MovieGenreID AS Expr1
FROM Movie
INNER JOIN
MovieGenreRelation ON Movie.MovieID = MovieGenreRelation.MovieID CROSS JOIN
Genre
ORDER BY NEWID()

Sorry if my English is bad :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:52:53

Your query doesnt look to contain any obvious errors. Are you getting any error?

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 06:58:37

No error it just writes

Shrek
Childrenmovie

and next time I refresh the page
Shrek
Comedy
next time
Romancemovie
Romantic

It is supposed to just take a random movie name out and write which genre this movie has below.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 07:06:02

quote:
Originally posted by Frenja

No error it just writes

Shrek
Childrenmovie

and next time I refresh the page
Shrek
Comedy
next time
Romancemovie
Romantic

It is supposed to just take a random movie name out and write which genre this movie has below.

and why do you think its not doing that? you get different values each time dont u?

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 07:15:53

Yes but to many different values ;)
I online need the moviename to be random and not the genre like it do right now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 07:18:52

quote:
Originally posted by Frenja

Yes but to many different values ;)
I online need the moviename to be random and not the genre like it do right now.

thats because you take CROSS JOIN with Genre table. You've not specified any condition for cross join so it will return all possible combination of Genre values with rest of query. thats why you get too many different values.

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 07:21:37

Any idea how I fix this?
I am a little new to sql and this problem have been bugging me all day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 07:24:18

quote:
Originally posted by Frenja

Any idea how I fix this?
I am a little new to sql and this problem have been bugging me all day

how can i have idea on that.

you need to identify how genre is related to movie data in your system.

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 07:29:13

I have a relationsdatabase ;)
MovieGenreRelation is the relation to movie and genre...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 07:36:31

Something similar to this

SELECT TOP 1	Movie.MovieID,
		Movie.MovieNavn,
		Movie.GenreID,
		Movie.MovieGenreID,
		Genre.GenreNavn
FROM		Movie
INNER JOIN	MovieGenreRelation ON MovieGenreRelation.MovieID = Movie.MovieID
INNER JOIN	Genre ON Genre.GenreID = MovieGenreRelation.GenreID
ORDER BY	NEWID()

E 12°55'05.63"
N 56°04'39.26"

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 08:08:03

quote:

Originally posted by Peso

Something similar to this

SELECT TOP 1	Movie.MovieID,
		Movie.MovieNavn,
		Movie.GenreID,
		Movie.MovieGenreID,
		Genre.GenreNavn
FROM		Movie
INNER JOIN	MovieGenreRelation ON MovieGenreRelation.MovieID = Movie.MovieID
INNER JOIN	Genre ON Genre.GenreID = MovieGenreRelation.GenreID
ORDER BY	NEWID()

E 12°55'05.63"
N 56°04'39.26"

When I try your example I get a error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 08:13:24

And this is the ONLY query you run?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 08:14:16

Try putting braces () around 1

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 08:15:20

I have tried with () around 1 :)
and yes this is the only query I run.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 08:16:11

Which tool are you using?

E 12°55'05.63"
N 56°04'39.26"

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 08:16:43

Microsoft SQL Express 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 08:20:25

That is the database engine.
Which QUERY TOOL are you using?

E 12°55'05.63"
N 56°04'39.26"

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 08:24:32

That tool there are in the program :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 08:29:33

The query works for me.
There must be something else you are not telling us.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 08:29:37

quote:
Originally posted by Frenja

That tool there are in the program :)

which is line the error points to?

Frenja
Starting Member

12 Posts

Posted - 2008-09-25 : 08:34:36

Line 2 :)
Peso I am telling what I know :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources