Select from two table with distinc

[rrifafauzikomara]

I have query SQL like this:

SELECT DISTINCT LST_KURS.LKU_ID, LST_KURS.LKU_SYMBOL FROM LST_PRODUCT_CALC join LST_KURS WHERE LST_PRODUCT_CALC.LKU_ID = LST_KURS.LKU_ID AND LST_PRODUCT_CALC.PSET_ID = #{psetId}

How to write that query on moor?

This is my table of LST_KURS:

import 'package:moor/moor.dart';

class LstKurs extends Table {
  @override
  String get tableName => 'LST_KURS';

  TextColumn get lkuId => text().named('LKU_ID')();

  IntColumn get lsneId => integer().named('LSNE_ID').nullable()();

  TextColumn get lkuSymbol => text().named('LKU_SYMBOL').nullable()();

  @override
  Set<Column> get primaryKey => {lkuId};
}

This is my table of lst_prodset_calc:

import 'package:moor_flutter/moor_flutter.dart';

class LstProdsetCalc extends Table {
  @override
  String get tableName => 'lst_prodset_calc';

  IntColumn get psetId => integer()
      .named('PSET_ID')
      .customConstraint('NULL REFERENCES lst_prodset (PSET_ID)')();

  TextColumn get lkuId => text().named('LKU_ID')();

  IntColumn get flagFactorPremium => integer().named('FLAG_FACTOR_PREMIUM').nullable()();

  IntColumn get minPremium => integer().named('min_premium').nullable()();

  IntColumn get maxPremium => integer().named('max_premium').nullable()();

  IntColumn get flagMinUp => integer().named('FLAG_MINUP').nullable()();

  IntColumn get flagMaxUp => integer().named('FLAG_MAXUP').nullable()();

  IntColumn get minUp => integer().named('min_up').nullable()();

  IntColumn get maxUp => integer().named('max_up').nullable()();

  IntColumn get lpfIdMinUp => integer().named('lpf_id_min_up').customConstraint(
      'NULL REFERENCES lst_prodset_form (lpf_id_min_up)')();

  IntColumn get lpfIdMaxUp => integer().named('lpf_id_max_up').customConstraint(
      'NULL REFERENCES lst_prodset_form (lpf_id_max_up)')();

  IntColumn get minTopup => integer().named('min_topup').nullable()();

  IntColumn get biayaAdminUlink => integer().named('biaya_admin_ulink').nullable()();

  @override
  Set<Column> get primaryKey => {psetId, lkuId};
}

This is my function in Dao:

Future<List<String>> getListKurs(int psetId) {
    // idk how to write that query on moor in here
  }

[simolus3]

If you want to load a full row with all columns from all tables, you could use

final query = select(lstProductCalc, distinct: true).join(
    [innerJoin(lstKurs, lstKurs.lkuId.equalsExp(lstProductCalc.lkuId))]);
query.where(lstProductCalc.psetId.equals(Variable(yourId)));

If you're only interested in some columns, you could use

final query = selectOnly(lstProductCalc, distinct: true)
  ..addColumns([lstKurs.lkuId, ...])
  ...join(
    [innerJoin(lstKurs, lstKurs.lkuId.equalsExp(lstProductCalc.lkuId))]);
query.where(lstProductCalc.psetId.equals(Variable(yourId)));

[rrifafauzikomara]

@simolus3 Thanks for the answer, this work.