Mario Mario - 2 years ago 76
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download