CODEWITHSUNDEEP
sqlsqlite
Arya
Arya

Reputation: 371

More than 1 result of sql query

I have sql query 

SELECT RV.duration, 
       P.expirytime 
FROM   rid_purchase_info_v64 AS P 
       INNER JOIN rid_video_asset_movievod AS RV 
               ON RV.rid = P.rid 
WHERE  RV.rid = 31041;

I am getting result

7166|1386308217

143|1386308217 Why am I getting 2 results instead of 1

Upvotes: 1

Views: 74

Answers (2)

Edper
Edper

Reputation: 9322

If you want to show only 1 row then try to use LIMIT, like:

 SELECT RV.duration, 
   P.expirytime 
 FROM   rid_purchase_info_v64 AS P 
   INNER JOIN rid_video_asset_movievod AS RV 
           ON RV.rid = P.rid 
 WHERE RV.rid = 31041
 LIMIT 1

Or if you want to get the highest value you could use an Aggregate function MAX():

 SELECT MAX(RV.duration),
   P.expirytime 
 FROM   rid_purchase_info_v64 AS P 
 INNER JOIN rid_video_asset_movievod AS RV 
           ON RV.rid = P.rid 
 WHERE RV.rid = 31041
 GROUP BY P.expirytime

Or for smallest value MIN():

 SELECT MIN(RV.duration),
   P.expirytime 
 FROM   rid_purchase_info_v64 AS P 
 INNER JOIN rid_video_asset_movievod AS RV 
           ON RV.rid = P.rid 
 WHERE RV.rid = 31041
 GROUP BY P.expirytime

Upvotes: 1

Milind
Milind

Reputation: 83

As far as I understand, the table rid_purchase_info_v64 is in one to many relationship with rid_video_asset_movievod table. There are more than one entries in RV related to P. Many developers put an attribute 'deleted' or 'status=disable' to hide the unwanted row.

What you need to do is identify such attribute and put in where statement. This is very common problem with beginners.

Upvotes: 1

Related Questions