MySQL - How to join multiple queries?

Question

I have the following query that gives me the correct results. The only problem is that it is giving me foreign keys for the last two columns it returns as they are from a different table. I would like to get the description that these Foreign Keys represent.

This query Returns Book Name, FK of the award the book received and FK of the organisation giving the award.

SELECT b.booktitle, ba.awbodyid, ba.awardid FROM Book b 
JOIN BookAward ba ON b.bookid = ba.bookid 
WHERE ba.bookid=4

How can I return the actual name of the award and the actual name of the organisation giving the award in the above query?

I have created the following two queries that is capable of returning these results but I need help in adding these queries to the query above.

The query below will return all the awards a book has received:

SELECT a.awardname FROM BookAward f 
JOIN Award a ON f.awardid = a.awardid
WHERE f.bookid=4

The query below will return the organisation that gave the award for the book:

SELECT DISTINCT a.awardbody FROM BookAward f 
JOIN AwardingBody a ON f. awbodyid = a. awbodyid
WHERE f.bookid=4

this should help https://stackoverflow.com/questions/6035901/joining-two-select-statements — BARNOWL, Nov 25 '17 at 21:13
check this too https://stackoverflow.com/questions/13400726/mysql-combine-two-queries — BARNOWL, Nov 25 '17 at 21:13

score 2 · Accepted Answer · answered Nov 25 '17 at 21:19

2

Something like this :

SELECT b.booktitle, a.awardname, ab.awardbody FROM Book b 
JOIN BookAward ba ON b.bookid = ba.bookid
JOIN Award a ON ba.awardid = a.awardid
JOIN AwardingBody ab ON ba.awbodyid = ab.awbodyid
WHERE ba.bookid=4

answered Nov 25 '17 at 21:19

marcvander

479
4
17

You are my superhero :)) Thanks that works! I tried something similar earlier but kept getting errors. Idk. – HoldTight Nov 25 '17 at 21:27

MySQL - How to join multiple queries?

1 Answers1