I tried to run this query in dbase database but subquery is not supported inside the left outer join .How to run this query in dbase with same data without using subquery inside the left outer join.
select sum(h.netto) netto, sum(h.brutto) brutto,
sum(h.netto*(1-h.zero_quote)) nettooHB,
sum(h.brutto*(1-h.zero_quote)) bruttooHB,
sum(h.menge) menge, artikelgruppe.nummer,
artikelgruppe.name, kasse.name oName,
kasse.nummer oNummer from hist_dat h
left outer join wg obergruppen on h.wg = obergruppen.nummer and obergruppen.is_og = true
left outer join kellner on h.kellner = kellner.nummer
left outer join mwst on h.mwst_kz = mwst.nummer
left outer join wg artikelgruppe
on h.sparte = artikelgruppe.nummer and artikelgruppe.is_og = false
left outer join kk outlet on h.kassen_kr = outlet.nummer
left outer join artikel on h.artikel = artikel.nummer
left outer join (select datum name, datum nummer from hist_dat where datum between ‘2019-05-28’ and ‘2019-06-01’ and kassen_kr between 1 and 2 group by datum ) datum on h.datum = datum.nummer
left outer join kassen kasse on h.kasse = kasse.nummer
left outer join (select rechnung nummer, rechnung name from hist_dat where datum between ‘2019-05-28’ and ‘2019-06-01’ and kassen_kr between 1 and 2 group by rechnung) rechnung on h.rechnung = rechnung.nummer
left outer join (select artikel nummer, name, rechnung from hist_dat where datum between ‘2019-05-28’ and ‘2019-06-01’ and kassen_kr between 1 and 2
and artikel <= 0 group by rechnung, artikel, name) zahlarten on h.rechnung = zahlarten.rechnung
where h.datum between ‘2019-05-28’ and ‘2019-06-01’ and h.kassen_kr between 1 and 2 and h.artikel >= 0
and obergruppen.is_kombi = false and obergruppen.is_auslage = false group by artikelgruppe.nummer, artikelgruppe.name, kasse.name, kasse.nummer order by kasse.nummer
Why do you have all of those joins in your query? You’re not pulling any of the data out of those LEFT OUTER JOINS so why go through all that extra processing. Only join what you’re pulling data from…
A LEFT OUTER JOIN means to try to attach a table (or sub-query) but if no match exists, show null in the place of the outer joined data. You have no outer joined data (it’s not even used in other joins…) - everything joins off the hist_dat table.
SELECT SUM(h.netto) netto
, SUM(h.brutto) brutto
, SUM(h.netto*(1-h.zero_quote)) nettooHB
, SUM(h.brutto*(1-h.zero_quote)) bruttooHB
, SUM(h.menge) menge
, artikelgruppe.nummer
, artikelgruppe.name
, kasse.name oName
, kasse.nummer oNummer
FROM hist_dat h
LEFT OUTER JOIN wg artikelgruppe ON h.sparte = artikelgruppe.nummer AND artikelgruppe.is_og = FALSE
LEFT OUTER JOIN wg obergruppen ON h.wg = obergruppen.nummer AND obergruppen.is_og = true
LEFT OUTER JOIN kassen kasse on h.kasse = kasse.nummer
WHERE h.datum BETWEEN ‘2019-05-28’ AND ‘2019-06-01’
AND h.kassen_kr BETWEEN 1 AND 2
AND h.artikel >= 0
AND obergruppen.is_kombi = FALSE
AND obergruppen.is_auslage = FALSE
GROUP BY artikelgruppe.nummer, artikelgruppe.name, kasse.name, kasse.nummer
ORDER BY kasse.nummer
If you look, the data return is exactly double what the data out of my query. This tells me that one of those left outer joins in your query is matching and returning two rows for each row that my version does. The reason you don’t see it is you’re not using any of the data from the double match.
Looking closer at your sub-queries…I don’t see how they’re possibly working as a query in the first place. This isn’t valid syntax that I’ve ever seen because the group isn’t being done on any of the fields being selected. Are you trying to do SELECT DISTINCT on those rows?
select datum name, datum nummer from hist_dat where datum between ‘2019-05-28’ and ‘2019-06-01’ and kassen_kr between 1 and 2 group by datum
