Göra krånglig fråga enklare.

2 svar · 195 visningar · startad av oJones 20 mars 2003

oJonesMedlem sedan dec. 200154 inlägg

Frågan20 mars 2003 13:24#1

Hej!

Jag har en fråga om hur jag ska kunna skriva om följande fråga så den inte blir så lång och tar sådan tid att få färdig.
Just nu tar den 10-20 sekunder innan den kan presentera mitt svar.

SQL = "SELECT DISTINCT movie.title, movie.Movie_Id, movie.number, director.director, actor.actor " & _
"FROM movie, director, director_movie, actor " & _
"WHERE " & _

"movie.title like '%" & Request.Querystring("searchword") & "%' " &\_
"AND director.director_id = director_movie.director_id " & \_
"AND movie.movie_id = director_movie.movie_id " & \_

"OR movie.number like '%" & Request.Querystring("searchword") & "%' " &\_
"AND director.director_id = director_movie.director_id " & \_
"AND movie.movie_id = director_movie.movie_id " & \_

"OR director.director like '%" & Request.Querystring("searchword") & "%' " &\_
"AND director.director_id = director_movie.director_id " & \_
"AND movie.movie_id = director_movie.movie_id " & \_

"OR actor.actor like '%" & Request.Querystring("searchword") & "%' " &\_
"AND director.director_id = director_movie.director_id " & \_
"AND movie.movie_id = director_movie.movie_id " & \_

"GROUP BY movie.title ORDER BY movie.title " & Request.Querystring("sort2")

Finns det något smidigare sätt?

aasahMedlem sedan mars 20034 471 inlägg

20 mars 2003 13:47#2

Re: Göra krånglig fråga enklare.

oJones skrev:

...SQL = "SELECT DISTINCT movie.title, movie.Movie_Id, movie.number, director.director, actor.actor " & _
"FROM movie, director, director_movie, actor " & _
"WHERE " & _

"movie.title like '%" & Request.Querystring("searchword") & "%' " &_
"AND director.director_id = director_movie.director_id " & _
"AND movie.movie_id = director_movie.movie_id " & _

"OR movie.number like '%" & Request.Querystring("searchword") & "%' " &_
"AND director.director_id = director_movie.director_id " & _
"AND movie.movie_id = director_movie.movie_id " & _

"OR director.director like '%" & Request.Querystring("searchword") & "%' " &_
"AND director.director_id = director_movie.director_id " & _
"AND movie.movie_id = director_movie.movie_id " & _

"OR actor.actor like '%" & Request.Querystring("searchword") & "%' " &_
"AND director.director_id = director_movie.director_id " & _
"AND movie.movie_id = director_movie.movie_id " & _

"GROUP BY movie.title ORDER BY movie.title " & Request.Querystring("sort2")
..

Säkert idiotisk fråga, men varför upprepar du samma villkor om och om igen? Behövs det i MySQL? Borde du inte kunna skriva:

SQL = "SELECT DISTINCT movie.title, movie.Movie_Id, movie.number, director.director, actor.actor " & _
"FROM movie, director, director_movie, actor " & _
"WHERE " & _
"director.director_id = director_movie.director_id " & _
"AND movie.movie_id = director_movie.movie_id " & _
"AND (
"movie.title like '%" & Request.Querystring("searchword") & "%' " &_
"OR director.director like '%" & Request.Querystring("searchword") & "%' " &_
"OR actor.actor like '%" & Request.Querystring("searchword") & "%' )" &_
"GROUP BY movie.title ORDER BY movie.title " & Request.Querystring("sort2")

Är ingen hejare på MySQL så ovanstående behöver inte alls funka, jag kan bara inte begripa hur MySQL kan veta hur den ska tolka prioriteringen mellan alla dina AND och OR...?

oJonesMedlem sedan dec. 200154 inlägg

20 mars 2003 15:34#3

Du har helt rätt =)

Jag gjorde lite justeringar så blev det dessutom ännu mer rätt:

SQL2 = "SELECT DISTINCT movie.title, movie.Movie_Id, movie.number, director.director, actor.actor " & _
"FROM movie, director, director_movie, actor " & _
"WHERE " & _
"director.director_id = director_movie.director_id " & _
"AND movie.movie_id = director_movie.movie_id " & _
"AND actor.actor_id = movie.movie_id " & _
"AND ( " & _
"movie.title like '%" & Request.Querystring("searchword") & "%' " &_
"OR director.director like '%" & Request.Querystring("searchword") & "%' " &_
"OR actor.actor like '%" & Request.Querystring("searchword") & "%' )" &_
"GROUP BY movie.title ORDER BY movie.title " & Request.Querystring("sort2")

Tack för hjälpen!