Subqueries

SQLBuilder

Subquery parameters accept a SQLBuilder, so you can use the full API.

Subquery in FROM

var kit = DBCash.useSQL(0);
var dt = kit
    .from("a", (d) => {
        d.select("d.DD_FFocued,r.HH_MdmRiskOID")
        .from("HH_MdmDangDeal d join HH_MdmRisk r on d.DD_RiskIssueId = r.MR_Id");
    })
    .where("a.idx=1")
    .query();

Subquery in JOIN

Pass join type and ON, then build the inner query:

var kit = DBCash.useSQL(0);
var dt = kit
    .select("a")
    .from("tableA as a")
    .join("left join","b on a.id=b.id", (t) => {
        t.select("name")
            .from("student")
            .where("id=1");
    })
    .top(1)
    .query();

WHERE IN subquery

The column and subquery must match (SQL requirement).

var kit = DBCash.useSQL(0);
var dt = kit
    .select("a.Name")
    .from("tableA as a")
    .whereIn("a.Name", (t) => { 
        t.select("Name")
            .from("student")
            .where("id=1");
    })
    .top(1)
    .query();

CTEs

Simple CTE:

var kit = DBCash.useSQL(0);
var dt = kit
    .withSelect("t1", "select a from t")
    .select("a")
    .from("t1")
    .top(1)
    .query();

With builder:

var kit = DBCash.useSQL(0);
var dt = kit
    .withSelect("t1", "select a from t")
    .withSelect("t2", (t) => { 
        t.select("b").from("b")
            .where("b.id=1");
    })
    .select("a")
    .from("t1")
    .top(1)
    .query();

SQLClip

Requires entities. Subqueries are mainly supported in conditions, e.g. whereIn:

var clip = DBCash.useClip(0);

var tar = clip.from<HHDutyItem>(out var d)
    .whereIn(() => d.Di_Name, (c) => { 
        return c.from<HHWordBag>(out var w)
        .where(() => w.Wb_Code, "a")
        .select(()=>w.Wb_Name);
    })
    .select(d)
    .queryList();