Base: query for report with multiple sub queries

[pascale]

Hi, I need to create a monthly report of sales, I have three tables which have have joins from primary keys to foreign keys, I am using debian 9 (crunchbang) Libreoffice 5 and embedded ( struggling to connect Mariadb to Libreoffice).

I import all the sales data from a CSV sheet and divide the sheet into sales, customers details, with the pseudo as primary/foreign key for customers to make the sales sheet smaller. I have the payment sheet separate (paypal) and have again separated customers from payments in a similar fashion. for tax purposes I need to separate sales out in the following fashion;
Home sales (France), EU Sales, and Export sales, to make things really complicated I sell antiques and hand made items, and I have postage costs, so I need to create a super query!:

postFR postEU postMONDE SalesMONDE SalesHandMadeFR SalesHandMadeEU SaleFRantiques SalesEUantique and Purchasevalueantiques

I have two tables for stock, one is hand made and the other is stock, but only stock is needed. With the fantastic help of ask.libreoffice I have been able to create various queries that gives me the information i need; below

main query SELECT DISTINCT "EBSales"."ID", "EBSales"."Payé le", "EBSales"."stock", "EBSales"."shades", "EBSales"."Quantité", CASE WHEN "EBSales"."shades" IS NULL THEN 0 ELSE "Quantité" * "Prix de vente" * "Rate" END AS "SHADES", CASE WHEN "EBSales"."stock" IS NULL THEN 0 ELSE "Quantité" * "Prix de vente" * "Rate" END AS "TTC", "EBSales"."Prix total", "paypal"."AvComm", "paypal"."Comm", "EBSales"."NDT", "paypal"."Remboursement", "EBSales"."Pays" FROM "EBSales", "paypal" WHERE "EBSales"."NDT" = "paypal"."NDT" AND MONTH( "EBSales"."Payé le" ) = :MOIS

This gives

Here are two of the six queries I need to add,

  'SELECT "EBSales"."shades", "EBSales"."Quantité", "EBSales"."Payé le" AS "Date", "Quantité" * "Prix de vente" * "Rate" AS "FR SHADES €", "EBSales"."Pays" FROM "EBSales", "paypal" WHERE "EBSales"."NDT" = "paypal"."NDT" AND "EBSales"."shades" IS NOT NULL AND "EBSales"."Pays" = 'France métropolitaine' AND MONTH( "EBSales"."Payé le" ) = :MOIS'

and

'SELECT "EBSales"."Payé le" AS "Date", "Livraison et expédition" * "Rate" AS "Poste MONDE €", "EBSales"."Pays" FROM "EBSales", "paypal" WHERE "EBSales"."NDT" = "paypal"."NDT" AND "EBSales"."Pays" <> 'Royaume-Uni' AND "EBSales"."Pays" <> 'Malte' AND "EBSales"."Pays" <> 'Irlande (Eire)' AND "EBSales"."Pays" <> 'Grèce' AND "EBSales"."Pays" <> 'Allemagne' AND "EBSales"."Pays" <> 'Chypre' AND "EBSales"."Pays" <> 'Pays-Bas' AND "EBSales"."Pays" <> 'Danemark' AND "EBSales"."Pays" <> 'Suisse' AND "EBSales"."Pays" <> 'Italie' AND "EBSales"."Pays" <> 'Luxembourg' AND "EBSales"."Pays" <> 'France métropolitaine' AND MONTH( "EBSales"."Payé le" ) = :MOIS'

The others are very similar and Once some one can point me in the right direction I can add the others.
I have spent the week searching including floppybunnies, wikis, nested queries, sub queries and more.
Help will be gratefully received.
Regards Paul

[pascale]

Hi, I have looked at your link and have tried as far as possible to follow this line of normalization, before starting this database, I read as many tutorial as possible, moving from calc to base is not easy, but I am willing to learn. I tried to add the other queries one at a time to the main query in various methods but each time i got syntax error messages or “DATA could not be loaded”. I can add a copy of the base if this helps, I am not asking some one to do the work but a pointer. paul

[Ratslinger]

Hello,

First, it is going to be difficult at best to give a definitive answer. Even with all the information it could take some time to figure this out. However, some information on direction can be provided.

Normalization (Wiki here) is not only important for databases but also for your queries. In stating you have the six additional queries, it seems if they are similar, their results can be gotten with a UNION statement and then applied to your main query. But there is another problem. In looking (briefly) at these last two queries I don’t see how the results relate back to the original query. There has to be something in common to tie them all together.

Don’t try to look at the final result. Take one small piece at a time like: Are the last two shown related? - combine them; the other four not shown similar? - combine them; UNION apply? This is how your other SQL questions were solved. Get result of a small part, get result of next pat, now combine them, move on to next part.

As for MariaDB, I have that set on a Debian 8 test partition with various connectors. Be glad to help with that but it should be a separate question. It is fairly simple once you understand different connectors. I typically use MySQL & PostgreSQL on my Mint 18.2 partition regularly. MySQL connection data is almost identical to that of MariaDB.

Edit:

Aside from my comment below, yet another look again shows no common ground between the three queries. It appears the second two are missing the selection of "EBSales"."ID" which seems would give you the needed connection among the queries. Once you have this common ground, you can JOIN the query results much like my answer in your previous question posted here.

[pascale]

Hi @Ratslinger thank you for coming back so quickly, I will take a look at the link tomorrow (midnight here) and get back, Thanks
Regards Paul

[Ratslinger]

@pascale You can add the Base file. Can’t say any final result will become of it. Looked a bit more at the three Queries provided. Just as an example of Normalizing. In ALL three (and probably the other four) these conditions need to be met:

WHERE "EBSales"."NDT" = "paypal"."NDT"
AND   MONTH("EBSales"."Payé le") = :MOIS

Why not start with a result set which has this done and eliminate the six other times you request it? Again just an example.

[Ratslinger]

Just remembered this post for connecting MariaDB - click here. Read all of it.

[pascale]

Well Thank you again Mr @Ratslinger, for your help and advise, and mainly not just giving me the answer when you could have, but pointing me in the right direction so that I was able to work it out for my self (only possible with the help you have given me with my last questions)
I stripped down all the extra queries as per

@pascale You can add the Base file. Can’t say any final result will become of it. Looked a bit more at the three Queries provided. Just as an example of Normalizing. In ALL three (and probably the other four) these conditions need to be met:

WHERE “EBSales”.“NDT” = “paypal”.“NDT”
AND MONTH(“EBSales”.“Payé le”) =:MOIS

Why not start with a result set which has this done and eliminate the six other times you request it? Again just an example.

i added to each “ID” from the sales table AND formed a LEFT JOIN between all, this then showed all results exactly as I wanted, I only learned this because I had to work out the answer myself, you did not give it to me on a plate!
Thank you so much!!!
Here is the answer!`

SELECT DISTINCT “EBSales”.“ID”, “EBSales”.“Payé le” AS “DATE”, “EBSales”.“Quantité” AS “QT”, “EBSales”.“shades” AS “LS”, “EBSales”.“stock” AS “ST”, “postFR”.“Poste FR €”, “postEU”.“Poste EU €”, “postMONDE”.“Poste MONDE €”, “EBSales”.“Prix total” AS “Prix en DEVISE”, “FRSHADES€”.“FR SHADES €” AS “FR LS €”, “EUSHADES€”.“EU TTC €” AS “EU LS €”, “FRTTC€”.“FR TTC €” AS “FR ST €”, “EUTTC€”.“EU TTC €” AS “EU ST €”, “MONDETTC€”." MONDE TTC €", “STOCKVT€”.“PrixdAchat”, “paypal”.“AvComm” + ( “REMVT”.“Net” * “paypal”.“Rate” ) AS “AvComm”, “paypal”.“Comm” * “paypal”.“Rate” AS “Comm €”, “REMVT”.“Net” * “paypal”.“Rate” AS “Rem €”, “paypal”.“NDT”, “EBSales”.“Pays” AS “Countries”, “EBSales”.“ID”, “paypal”.“Remboursement” FROM { oj “EBSales” LEFT OUTER JOIN “EUTTC€” ON “EBSales”.“ID” = “EUTTC€”.“ID” LEFT OUTER JOIN “EUSHADES€” ON “EBSales”.“ID” = “EUSHADES€”.“ID” LEFT OUTER JOIN “FRSHADES€” ON “EBSales”.“ID” = “FRSHADES€”.“ID” LEFT OUTER JOIN “FRTTC€” ON “EBSales”.“ID” = “FRTTC€”.“ID” LEFT OUTER JOIN “postFR” ON “EBSales”.“ID” = “postFR”.“ID” LEFT OUTER JOIN “postEU” ON “EBSales”.“ID” = “postEU”.“ID” LEFT OUTER JOIN “postMONDE” ON “EBSales”.“ID” = “postMONDE”.“ID” LEFT OUTER JOIN “MONDETTC€” ON “EBSales”.“ID” = “MONDETTC€”.“ID” LEFT OUTER JOIN “STOCKVT€” ON “EBSales”.“stock” = “STOCKVT€”.“Libellé personnalisé” }, { oj “paypal” LEFT OUTER JOIN “REMVT” ON “paypal”.“Remboursement” = “REMVT”.“NDT” } WHERE “EBSales”.“NDT” = “paypal”.“NDT” AND MONTH( “EBSales”.“Payé le” ) = :MOIS

Thank you again Mr @Ratslinger

Kind regards

Paul

[Ratslinger]

Very well done! Yes, you are catching on. Always glad to help but nice when someone takes initiative to work out the problem. I believe you learn more and it sticks with you longer this way.

As for MariaDB, if you still have problems after trying the provided link, ask as new question. Surely can get you up and running.