Mario Mario - 7 months ago 12
SQL Question

MySQL join with 3 tables

I have tree tables

Table USER: PK (IDU)

IDU nameUser
---------------
3 "ImUser1"
4 "ImUser2"


Table BOOK: PK (IDB)

IDB nameBook
---------------
20 "ImBook"


Table RECOMMENDATION:PK (IDR, idUserSource, idUserDest, idBook)

IDR idUserSource idUserDest idBook
-------------------------------------------
1 3 4 20


Can anybody please tell how to make a view or query prints like this? I'm using hibernate, but is not indispensable at all.

IDR idUserSource idUserDest idBook nameUser nameBook
-------------------------------------------------------------------
1 3 4 20 "ImUser1" "ImBook"


Thanks in advance.

Answer

Subquery is easiest way for what you want to do:

select r.IDR, r.idUserSource, r.idUserDest, r.idBook,
(select nameUser from USER where r.idUserSource=IDU) as nameUser,
(select nameBook from BOOK where r.idBook=IDB) from RECOMMENDATION r