www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] Multiple join problem

  1. #1
    Join Date
    Aug 2010
    Location
    Ohio
    Posts
    136

    resolved [RESOLVED] Multiple join problem

    Ok so I have the following tables:
    Code:
    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.

  2. #2
    Join Date
    Oct 2010
    Location
    Ohio
    Posts
    233
    Code:
    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!
    Last edited by Derokorian; 04-25-2011 at 02:49 PM. Reason: failed /code

  3. #3
    Join Date
    Aug 2010
    Location
    Ohio
    Posts
    136
    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:
    Code:
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles