Liferay Dynamic Subquery on two tables

Question

I need to access two attributes from two different tables via dynamic subquery.

create table DLFileVersion (
    uuid_ VARCHAR(75) null,
    fileVersionId LONG not null primary key,
    groupId LONG,
    companyId LONG,
    userId LONG,
    userName VARCHAR(75) null,
    createDate DATE null,
    etc... etc... etc...
    status INTEGER,
    statusByUserId LONG,
    statusByUserName VARCHAR(75) null,
    statusDate DATE null
);

and other table

create table DLFileEntry (
    uuid_ VARCHAR(75) null,
    fileEntryId LONG not null primary key,
    groupId LONG,
    companyId LONG,
    userId LONG,
    userName VARCHAR(75) null,
    version VARCHAR(75) null,
    size_ LONG,
);

I have to find a file by attributes from these two tables, the problem is, that I don't know how to make dynamic subquery with two attributes from DLFileVersion table which are status and userName ... I need to combinate these two attributes with whole DLFileEntry table, to perform a dynamic query using criterions obtained by user from a jsp page. Example:

SELECT DLFileVersion.userName, DLFileEntry.userName, status, version
FROM DLFileVersion, DLFileEntry
WHERE ....

Answer 1

Take a look at Custom SQL for joins:

https://www.liferay.com/documentation/liferay-portal/6.2/development/-/ai/develop-custom-sql-queries-liferay-portal-6-2-dev-guide-04-en

http://www.liferaysavvy.com/2013/02/getting-data-from-multiple-tables-in.html