[SOLVED] Join query with 3 tables

[danik69]

hi, i have 3 tables

 

table books

 

mysql> select * from books;

+-------+-------------------------------------------------+--------------------------+-----------+--------------------------+----------------------+------------+-------+

| idcol | title                                          | author                  | category  | cover                    | publisher            | isbn      | price |

+-------+-------------------------------------------------+--------------------------+-----------+--------------------------+----------------------+------------+-------+

|    1 | C++ For Dummies                                | Stephen Radny Davis      | Computing | c++fordummies.jpg        | for Dummies          | 1234567891 | 24.99 |

|    2 | Servlets & JavaServer Pages                    | Marty Hall & Larry Brown | Computing | javaservletpages.jpg    | Prentice Hall        |  130092290 | 23.99 |

|    3 | Macromedia Director MX 2004 Bible              | Robert Martin            | Computing | directorbible.jpg        | Wiley                |  764569902 | 31.99 |

|    4 | Basic Statistics for Primer Biomedical Sciences | Olive Jean Dunn          | Science  | basicstatsbiomedical.jpg | Wiley Interscience  |  471354228 | 49.99 |

|    5 |  Molecular Biology of the Cell                  | Bruce Alberts            | Science  | molecularbio.jpg        | Garland Publishing  |  815340729 | 39.75 |

|    6 |  Biochemistry 5th Ed                            | Lubert Stryer            | Science  | biochemistry.jpg        | W H Freeman & Co Ltd |  716746840 | 41.99 |

|    7 | Microbiology                                    | Lancing Prescott        | Science  | microbilogy.jpg          | William C Brown      |  697293904 | 29.99 |

+-------+-------------------------------------------------+--------------------------+-----------+--------------------------+----------------------+------------+-------+

 

table book2readinglist

 

 

mysql> select * from book2readinglist;

+-------+------+-------------+

| idcol | book | readinglist |

+-------+------+-------------+

|    1 |    1 |          1 |

+-------+------+-------------+

1 row in set (0.00 sec)

 

table readinglist

 

 

mysql> select * from readinglist;

+-------+----------------------+

| idcol | degree              |

+-------+----------------------+

|    1 | Internet Computing  |

|    2 | Bio-Medical Science  |

|    3 | Physical Geography  |

|    4 | General History      |

|    5 | Multimedia Computing |

+-------+----------------------+

5 rows in set (0.14 sec)

 

i want to output all the books related to that readinglist eg the book number 1 is in readinglist 1 and is idcol 1 c++ for dummies

 

How can i write a query to do this?

 

Thankyou

 

Nick

 

 

[fenway]

You want all books on the same reading list as idcol = 1 from the books table?

[danik69]

Im basically want to output the details of any books that are linked to that readinglist

 

so idcol in the book table is the book id

 

so in the book2readinglist the book column is the idcol and then reading list column is whatever readinglist that book is on eg computing

 

so i want all the books that are say on internet computing reading list

[Barand]

SELECT b.*
FROM books b
    INNER JOIN book2readinglist br ON b.idcol = br.book
    INNER JOIN readinglist r ON br.readinglist = r.idcol
WHERE r.degree = 'Internet Computing'

 

If you already know the readinglist id (from a dropdown, say) then you only need two tables

 

SELECT b.*
FROM books b
    INNER JOIN book2readinglist br ON b.idcol = br.book
WHERE br.readinglist = '$listid'

[danik69]

Thankyou very much! Yeah i have used with a drop down box and it works superb! Thanks for your time! much appreciated.

 

Nick