Wordpress wpdb select from multipe tables

Question

Hi.

I have a three tables for my wordpress plugin.

videos = id, name playlists = id, name video_playlist = id, video_id, playlist_id

how do I get multiple results for multiple tables.

ie, I am busy editing a playlist and would like to display all videos in the playlist.

so the ID for the playlist you are viewing is passed and that is referenced against the video_playlist table to obtain all the video IDs.

Now to take it one step further I would like to also display the names for the Videos.

Here is what I currently have.

<?php if(isset($update)) {      
    $rows = $wpdb->get_results("SELECT * FROM $table_play_vid WHERE playlist_id = $update->id");  
    foreach($rows as $row){  
        echo $row->video_id;  
    }} ?>

Answer 1

I think it's a common MySQL query.

SELECT thistable.column, thattable.column FROM thistable,thattable WHERE thistable.something = thattable.something

Answer 2

Try something like this.

?php if(isset($update)) {
$rows = $wpdb->get_results("SELECT vp.video_id, v.name FROM $table_play_vid vp, videos v WHERE vp.playlist_id = $update->id and vp.video_id=v.id");
foreach($rows as $row){    
echo $row->video_id." ".$row->name;
}} ?>