0

The story behind:

I have a transactions' table where i have transactions, income and outgoing, this table also contents some private transactions i don't want to have (they are marked with a int field), my idea was now make 2 Views, 1 for incoming money and one for outgoing and exclude the private stuff over the where in the view.

So I have now 2 views, income_month_year and outcome_month_year

both have the structure:

ym  ms  quarter ym 
2022-09 116.56000   Q3-2022

I can have a month income and outgoing and some months only incoming or outgoing.

This sounds like a job for a full join, but on this I fail with "please check syntax", left and right joins work.

SELECT * FROM income_month_year inC FULL JOIN outcome_month_year outG ON inC.ym = outG.ym;

ym is a Varchar created in the view with DATE_FORMAT(record_date, '%Y-%m'), source fields are datetimes

Maybe on this way its also possible todo a calculation of the values?

thanks

StefanBD
  • 334
  • 3
  • 14
  • 1
    MySQL does not support FULL JOIN. Either gather all dates values from both tables (UNION DISTINCT in the subquery) then leftjion another tables copies to this list or expand the rowsets to common structure adding needed columns with zeros then group their UNION ALL. – Akina Oct 30 '22 at 13:26
  • ah kay thank you now i see FULL JOIN is the same like FULL OUTER JOIN was think its a different one and MSQL dont support only the outer not, anyway then i will solve my problems over php directly . – StefanBD Oct 30 '22 at 13:30
  • MSQL not exists. There are MySQL and MS SQL (SQL Server) - two different DBMSs. – Akina Oct 30 '22 at 13:40
  • sorry forget hit the Y Key, anyway this is solved now – StefanBD Oct 30 '22 at 13:45
  • 1
    views usually just make your queries more confusing as to what they are actually doing. splitting the table into two views and joining them makes no sense when you can just read the table. but in any case, please edit your question to show output of `show create table transactions;`, `show create view outcome_month_year;`, and `show create view income_month_year` and explain what calculation of values you want to do. – ysth Oct 30 '22 at 14:23
  • 1
    @ysth i think this would be a topic for another question (i currently don't need todo). And yes, you're completely right with the views and making stuff complex so i think this question is now a kind of "dont do" I will remember ur tips when i need again help and supply the create commands of the tables i use :) – StefanBD Oct 30 '22 at 14:33
  • @Akina, FYI mSQL was a product in the 1990's but it never grew popular. https://en.wikipedia.org/wiki/MSQL Technically it still does exist, it has had a release as recently as 2021. – Bill Karwin Oct 30 '22 at 17:33
  • @BillKarwin AFAIR miniSQL does not support any JOIN other than INNER one. – Akina Oct 30 '22 at 18:23

0 Answers0