Show list of custom posts with respective attachments?

Question

I would like to construct a query to show a list of custom posts ("podcasts") with the urls to their respective attachments. Each custom post_type ("podcast") has 1 attachment (the mp3 file belonging to it's post_parent). I would like an unordered list showing podcast's title, and it's attachment url (guid of the attachment) like this:

<ul>
<li>Podcast Title - http://..../podcast.mp3</li>

I achieved this with wp queries:

<?php query_posts('showposts=1&post_type=podcasts'); ?>
<ul>
<?php while (have_posts()): the_post(); ?>
<li><?php the_title(); ?><br>
<?php
$args = array( 'post_type' => 'attachment', 'post_parent' => $post->ID );
$attachments = get_posts($args);
if ($attachments) {
  foreach ( $attachments as $attachment ) {
  echo $attachment->guid;
  }
} ?>
</li>
<?php endwhile; ?>
</ul>

But I would like to know how I solve with raw SQL query. I've tried so many queries it's made me dizzy - any help would be amazing!

* EDIT: Decided to go with John's suggestion of WordPress' queries. Thank you and I trust someone else will also find this useful in future.

Answer 1

Trust us on this one, you want to use WordPress' query solution. It provides a whole bunch of awesome extras, such as object caching, autoloading of metadata, etc. Plus, it's almost guaranteed to be forwards compatible.

So. How to do what you've inquired about. Something like this ought to do the trick:

<?php
$podcasts = new WP_Query( array(
  'post_type' => 'podcasts',
  'posts_per_page' => 10, // the number of podcasts you want to show. use -1 for all of them.
));
foreach ( $podcasts as $podcast ) {
  $attachment = get_posts('post_type=attachment&post_status=inherit&post_parent='.$podcast->ID);
  if ($attachment) {
    $attachment = $attachment[0];
    ?>
    <li><?php echo get_the_title( $podcast->ID ); ?> - <?php echo wp_get_attachment_url( $attachment->ID ); ?></li>
    <?php
  }
}
?>