CODEWITHSUNDEEP
mysqlrelational-database
user3808307
user3808307

Reputation: 1471

How to do a MySQL query with these relational tables

I have the following tables

videos

id | title | url


video_to_category

video_id | category_id

I would like a query to get once all videos from the video table that have a certain category_id

Upvotes: 1

Views: 38

Answers (5)

user2560539
user2560539

Reputation: 

SELECT * FROM `videos` AS `v`,`video_to_category` AS `vtc`
WHERE `v`.`id`=`vtc`.`video_id`
AND `vtc`.`category_id`='YOUR CHOICE'

OR

SELECT * FROM `videos` `v` INNER JOIN `video_to_category` `vtc` 
ON `v`.`id`=`vtc`.`video_id`
AND `vtc`.`category_id`='YOUR CHOICE'

Example

Upvotes: 1

Asif Uddin
Asif Uddin

Reputation: 409

 select vi.id, vi.title, vi.url from videos vi left join video_to_category 
 vc on (vi.id = vc.video_id) where vc.category_id = '$category_id'

$category_id is the variable where you put your given variable id. I use "$" cause i use php varaiable here.

Thanks

Upvotes: 1

Syed Talha Hai
Syed Talha Hai

Reputation: 718

select videos.* from videos 
inner join video_to_category 
on video_to_category.video_id = videos.id 
and video_to_category.category_id = 10

By analyzing your tables it seems what your column video_to_category have only 2 columns, if this is the case then you do not need to save it in different column, you can add a new column in videos table name 'category_id', but if you have some other columns hidden in this question then leave it.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You can use inner join 

select a.* 
from videos as a 
inner join video_to_category as b on a.id = b.video_id
and b.category_id = 'Your_value';

Upvotes: 1

ghaziksibi
ghaziksibi

Reputation: 471

SELECT * FROM videos v, video_to_category vc
WHERE v.id=vc.video_id AND category_id=YOURID

Upvotes: 1

Related Questions