How to join tables using Query DSL

Question

I am using queryDsl for complex search queries in my application. I am new to querydsl. I have started with the below code to fetch few rows from one single table(TableA). But I have to find list of person(Count) with the same id in some other table(TableB)

public static Predicate find(final Long pId)
    QPerson qPerson = QPerson.person;
    Predicate predicate; 
    BooleanBuilder booleanBuilder = new BooleanBuilder();
    if (pId != null) {
        booleanBuilder.or(qPerson.person_no.eq(pId));
    } 
    if (name != null && !name.isEmpty()) {
        booleanBuilder.or(qPerson.expiry_dt.eq(name));
    }
    predicate = booleanBuilder.getValue();   
    return predicate;
    }

TableA:

pId         name

1001      sampleNameA
1002      sampleNameB
1003      sampleNameC

TableB:

pId        name       interests

1001     sampleNameA    music   
1001     sampleNameA    dance
1001     sampleNameA    coding
1003     sampleNameC    music   
1002     sampleNameB    dance
1002     sampleNameB    coding

I need to get output like this with below query

select cnt cnt, tableA.* from master_person_table tableA,(select count(*) cnt from tableB WHERE pId = '1002') cnt WHERE pId = '1002'

OUTPUT:

  count  pId        name       
    2    1002   sampleNameB    

I need to display the no of rows (for id=1002) in my HTML.

Can anyone please help me in finding the count of the pId to be fetched from tableB

Thanks in advance

Answer 1

Please try below query:-

1.For multiple pIds:-

SELECT COUNT(*) AS COUNT, pId, name FROM TableB GROUP BY pId

2.For only selected pId:-

SELECT COUNT(*) AS COUNT, pId, name FROM TableB WHERE pId = 1002

If u need more help plz let me know.