msm msm - 5 months ago 14
SQL Question

sql query involving 3 tables

I have 3 tables just like:-

Table_1 Table_2 Table_3
------------------------ -------------------- --------------------
id bk_title strm_id bk_no bk_isbn s_id strm_name
----------------------- --------------------- -----------------------
1 A_Book 3 1 ISBN0001 3 Science
2 B_Book 4 1 ISBN0002 4 History
2 ISBN0003


I want to fetch records as

BK_Title Num_Copies Stream
---------------------------------------
A_Book 2 Science
B_Book 1 History


How do i do so.Please advice.

Answer

Try this:

SELECT BK_Title, COUNT(Table_2.bk_isbn) AS Num_Copies, Table_2.strm_name AS Stream
FROM Table_1
JOIN Table_2 on (Table_1.id = Table_2.bk_no)
JOIN Table_3 on (Table_1.strm_id = s_id)
GROUP BY BK_Title, strm_name
ORDER BY BK_Title
Comments