+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: please help me, how to write a query for this logic? (/showthread.php?tid=58414)
please help me, how to write a query for this logic? - El Forum - 06-10-2013
[eluser]Unknown[/eluser]
Hi,
I am having 3 mysql tables,
1. category
2. video
3. videoCategory
category table schema as,
id name
----------
1 Hollywood
2 Bollywood
3 Kollywood
Video table schema as,
id title addedDate
-----------------------
1 video1 2013-06-11
2 video2 2013-06-11
3 video3 2013-06-06
4 video4 2013-06-06
5 video5 2013-06-04
6 video6 2013-06-04
7 video7 2013-06-03
8 video8 2013-06-02
9 video9 2013-06-02
10 video10 2013-06-01
videoCategory table schema as,
videoId categoryId
---------------------
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
4 2
5 2
6 3
7 3
8 2
8 3
I have to write the query to collect latest added top most 2 videos under each category in a single query.
Note : Single video may belong to one or more category.
Thanks for your help in advance.
please help me, how to write a query for this logic? - El Forum - 06-10-2013
[eluser]Cosai[/eluser]
select * from video
inner join video.id=videocategory.videoId
group by videoCategoty.categoryId
order by video.addedDate desc
limit 0,2
something like that
please help me, how to write a query for this logic? - El Forum - 06-10-2013
[eluser]Unknown[/eluser]
Hi Cosai,
Your query is returning only 2 rows, I am in need to 2 rows(latest added) from each category. If it has 3 categories, the query should return 6 videos, 2 videos from each category.
Thanks.