CODEWITHSUNDEEP
mysqlsql
nandha kumar
nandha kumar

Reputation: 303

How to access a row on a table based on a value from another table?

I am doing a mini project using MySQL. I came with the following problem:

I created 2 tables, student and book with 6 and 5 columns respectively.

    mysql> select * from book;
+--------+------+------------+---------+------+
| bookid | Name | Authorname | edition | cost |
+--------+------+------------+---------+------+
| cc12   | dbms | guna       |       5 |  500 |
| cc34   | CA   | fasil      |       5 |  600 |
| cs113  | OS   | rohan      |       3 |  300 |
| cs12   | AI   | ganesh     |       2 | 1000 |
| cs343  | c#   | jackesh    |       4 |  300 |
+--------+------+------------+---------+------+
5 rows in set (0.00 sec)

mysql> select * from studentbook;
+-----+--------+-----------+
| Sno | bookid | Studid    |
+-----+--------+-----------+
|   1 | cc12   | 14vec1088 |
|   2 | cs113  | 14vec1099 |
|   3 | cc34   | 14vec1132 |
|   4 | cs343  | 14vec2011 |
|   5 | cs12   | 14vec100  |
+-----+--------+-----------+
5 rows in set (0.00 sec)

Now, when I enter any of the studid mentioned in the table studentbook (This is performed by PHP in the backend) it should display the details of book associated with the respective studid from the table book.

How can I perform the above using MySQL Query?

Upvotes: 0

Views: 64

Answers (2)

Pedo
Pedo

Reputation: 194

This might be work.

SELECT * FROM book
WHERE bookid IN 
(SELECT bookid FROM studentbook 
WHERE studid = "[Id of which you want book]");

Upvotes: 3

Neil Kennedy
Neil Kennedy

Reputation: 603

This should get you what you need.

SELECT b.* 
FROM book b
INNER JOIN studentbook sb on b.bookid = sb.bookid
WHERE sb.Studid = [your id]

Upvotes: 1

Related Questions