CODEWITHSUNDEEP
mysql
Janaki
Janaki

Reputation: 145

Query return more than one row?

I am creating pagination for alphabet A-Z using php with mysql. I am having single record in my tables for alphabet A. When I querying the record, it will return more than one row instead of single row. Also linked tables contains only single row of record for A's movie id. There is no one to many relationship in any tables. Kindly correct by below query if I did any mistakes in that. Thanks in Advance.

select 
    M.name, 
    M.id, (select year(M.released_date))as year, 
    MS.storyrating,
    S.screenplayrating,   
    MS.dialoguerating, 
    MS.directionrating,
    MS.musicrating, 
    MS.bgmrating, 
    MS.cinematographyrating, 
    MS.characterizationrating, 
    MS.shotlocationrating, 
    MS.editingrating, 
    MS.productionrating,
    MI.director,
    MI.musicdirector 
from tttbl_movie M, tttbl_movie_info MI, tttbl_movie_score MS 
where M.id=MI.movie_id 
    and M.id=MS.movie_id 
    AND M.name 'A%' OR M.name 'a%' 
ORDER BY M.name ASC LIMIT 0,2;

Upvotes: 0

Views: 53

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

You need include the LIKE keyword and also I think you should put parenthesis for the OR part

where M.id=MI.movie_id 
  and M.id=MS.movie_id 
  and (M.name LIKE 'A%' OR M.name LIKE 'a%')
ORDER BY M.name ASC;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You have not like clause in your code 

select 
 M.name, 
 M.id, 
 year(M.released_date  as year, 
 MS.storyrating,
 S.screenplayrating,   
 MS.dialoguerating, 
 MS.directionrating,
 MS.musicrating,
 MS.bgmrating, 
 MS.cinematographyrating, 
 MS.characterizationrating, 
 MS.shotlocationrating, 
 MS.editingrating, 
 MS.productionrating,
 MI.director,
 MI.musicdirector 
 from tttbl_movie M, tttbl_movie_info MI, tttbl_movie_score MS 
  where M.id=MI.movie_id 
 and M.id=MS.movie_id 
 AND M.name  like 'A%' OR M.name like 'a%' ORDER BY M.name ASC LIMIT 0,2;

and if you need it eventually limit 1

LIMIT 1;

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Try to change your ORDER BY like

ORDER BY M.name ASC LIMIT 1;

Also you are missing the like in your OR clause which I assume is a typo since you say that you are getting 2 records.

Upvotes: 0

Related Questions