[RESOLVED] Multiple join problem

[NoasIT]

Ok so I have the following tables:

CREATE TABLE IF NOT EXISTS `reading_authors` (
  `id_author` int(11) unsigned NOT NULL auto_increment,
  `author_fname` varchar(50) NOT NULL,
  `author_lname` varchar(50) NOT NULL,
  `author_qual` varchar(10) NOT NULL,
  PRIMARY KEY  (`id_author`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=50 ;


CREATE TABLE IF NOT EXISTS `reading_books` (
  `id_book` mediumint(5) unsigned NOT NULL auto_increment,
  `book_title` varchar(100) NOT NULL,
  `book_has_link` tinyint(1) NOT NULL default '0',
  `book_link` varchar(150) default NULL,
  `book_cat` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id_book`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=53 ;

CREATE TABLE IF NOT EXISTS `reading_rel` (
  `book_id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  KEY `books_id` (`book_id`),
  KEY `author_id` (`author_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Adding books, or authors is working as expected, so is selecting a list of books with their authors. However In the part where I want to be able to delete books and authors, I'm having a problem.. I want to after say deleting author X, do you also want to delete the books by that author? But I want to list the book AND any other authors associated with the book (that way if you delete author x but author y helped with a book maybe you don't want to delete that book. The problem I'm having is selecting the books written by author x, along with all authors associated with that book.

Its obviously easy to find the books by the author
select * from reading_rel JOIN reading_books on books_id=id_book WHERE author_id=$id
What I'm having a problem with is also selecting the other authors... Any help would be greatly appreciated... If i have to resort to multiple queries that would make me sad but I'll do it. Thanks in advance.

[Derokorian]

SELECT b.*,a.* FROM reading_rel
   JOIN reading_books as b ON b.id_book = reading_rel.book_id
   JOIN reading_authors as a ON a.id_author = reading_rel.author_id
   WHERE reading_rel.book_id IN 
        (SELECT book_id FROM reading_rel
        WHERE author_id = $id)

Untested, good luck!

[NoasIT]

Sweet that works perfectly! Also, I'm wanting to do the reverse, if I delete a book I want to see the authors of that book, and maybe books written by them, so this is how I did that using your query:

SELECT b . * , a . * 
   FROM reading_rel
   JOIN reading_books AS b ON b.id_book = reading_rel.book_id
   JOIN reading_authors AS a ON a.id_author = reading_rel.author_id
   WHERE reading_rel.author_id IN 
      (SELECT author_id
         FROM reading_rel
         WHERE book_id =3)

Thanks again, hope someone finds this useful.