Jump to content

Recommended Posts

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

 

 

Link to comment
https://forums.phpfreaks.com/topic/84326-solved-join-query-with-3-tables/
Share on other sites

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

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'

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.