Subquery is not supported in dbase database

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,, 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,,, kasse.nummer order by kasse.nummer

Hi @sapnasemwal021 and a warm welcome to the forum.

I assume from the variable names that English is not your first language?

The title is a statement and not a problem that requires solving or the results from running the query.

Try adding the following Mysql error validation and report back with the results. I assume PHP error reporting is set and there are no syntax errors.

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
	 , 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,,, kasse.nummer 
 ORDER BY kasse.nummer

DaveMaxwell,Thanks for the help. I had already tried same query but I don’t get same output from both query.


And the Data of query Which I have issue :

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 

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.