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

check this too stackoverflow.com/questions/13400726/mysql-combine-two-queries — BARNOWL
– BARNOWL, Commented Nov 25, 2017 at 21:13

marcvander · Accepted Answer · 2017-11-25 21:19:53Z

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, 2017 at 21:19

marcvander

6577 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HoldTight Over a year ago

You are my superhero :)) Thanks that works! I tried something similar earlier but kept getting errors. Idk.

Collectives™ on Stack Overflow

MySQL - How to join multiple queries?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related