Material Variants

SQL-Vorlage aus der reybex-Standardbibliothek – für BAS23-Exportjobs und Ad-hoc-Tests.

← Alle Beispiel-SQLs

Abfrage

SELECT
material.id,
material.mat_code,
material.sku,
materialVatKey.name,
currency.code,
vendor.name,
grossWeightUnit.name,
netWeightUnit.name,
warehouseUnit.name,
materialType.name,
materialEnergyEfficiency.name,
parentMatGroup.name,
matGroup.name,
brand.name,
matStatus.name,
SUM(stockList.total) as material_stock
FROM material as material
LEFT JOIN material_variant as materialVariant on materialVariant.material_id = material.id AND materialVariant.is_delete != 1
LEFT JOIN stock_list as stockList on stockList.material_id = material.id
LEFT JOIN ltt_mat_status as matStatus on matStatus.id = material.mat_status_id
LEFT JOIN ltt_brand as brand on brand.id = material.brand_id
LEFT JOIN ltt_mat_group as matGroup on matGroup.id = material.mat_group_id
LEFT JOIN ltt_mat_group as parentMatGroup on parentMatGroup.id = matGroup.id
LEFT JOIN ltt_material_energy_efficiency as materialEnergyEfficiency on materialEnergyEfficiency.id = material.material_energy_efficiency_id
LEFT JOIN ltt_material_type as materialType on materialType.id = material.material_type_id
LEFT JOIN ltt_unit as warehouseUnit on warehouseUnit.id = material.warehouse_unit_id
LEFT JOIN ltt_unit as netWeightUnit on netWeightUnit.id = material.netweight_unit_id
LEFT JOIN ltt_unit as grossWeightUnit on grossWeightUnit.id = material.grossweight_unit_id
LEFT JOIN customer as vendor on vendor.id = material.vendor_id
LEFT JOIN ltt_currency as currency on currency.id = material.currency_id
LEFT JOIN ltt_vat_key as materialVatKey on materialVatKey.id = material.vat_key_id
WHERE material.tenant_id = :tenantId AND material.is_delete = 0
GROUP BY material.id;