CODEWITHSUNDEEP
javasql
reiley
reiley

Reputation: 3761

Reduce the number of query execution

I've a DataModel viz. MyDataObject.

Each MyDataObject contains 2 data types, viz. A & List<BC>.

That is:

class MyDataObject {

    String A;
    List<BC> bc;
}

class BC {
    String B;
    String C;
}

Now, I'm fetching data from database. Stucture of DB is like:

A can have multiple B, and

there is one-to-one mapping between B & C (c can be null, so single query join is nogood here).

In first query I'm fetching A & List using join. The resultset is like:

A1 B1
A1 B2
A1 B3
A2 B4
A2 B5....

In second query I've to fetch each C for B, i.e.

B1 C1
B2 C2
B3 C3....

Problem is in first query resultset, i'm able to set A & List, like

for (Result result: resultSet) {
    if (myData.getA == null) {
        myData.seta(result.getA());
    }
    myData.setB(result.getB);
}

But for second query, I need to pass B's id as argument in where clause to fetch Cs.

So, for each A, I've to execute a query & pass its B to fetch C.

How can I do this in query and later set C in data-type BC in minimum number of steps?

Upvotes: 0

Views: 63

Answers (1)

DaaaahWhoosh
DaaaahWhoosh

Reputation: 379

SELECT ab.A, ab.B, c.C
FROM 
(SELECT a.A, b.B, b.id FROM A a JOIN B b ON a.id = b.id) ab
LEFT JOIN C c ON ab.id = c.id

If I understand you correctly, this should give you everything you need in one query. Updated to deal with nullable C, though I am much less sure of it now. Updated again, this should make the left join work.

Upvotes: 1

Related Questions