Help with query in SQLite

[cbarbal]

Hi all,

I put this same query, more than 20 days ago, in https://es.stackoverflow.com/questions/ ... 456_206097 and they have not given me any solution. I put it in this forum because it has to do with the program that I intend to move from FileMaker to LiveCode. If a moderator considers that it is not the right place for this consultation and eliminates it, I will understand it perfectly.

I want to obtain income and expenses from each Property and year. The tables involved are the following: Fincas (Property), Lloguers (Income), Despeses (Expenses).

If I do the two queries separately they work correctly

Code: Select all

SELECT f.finca Property, l.anyo Year,  sum(l.total) Income
FROM Finques f
LEFT JOIN Lloguers l ON l.fincaID = f.fincaID
GROUP BY f.finca, l.anyo
ORDER by f.finca, l.anyo

83 Records. Records and totals OK

Code: Select all

SELECT f.finca Property, d.anyo year, sum(d.total) Expenses
FROM Finques f
LEFT JOIN Despeses d ON f.fincaID = d.fincaID 
GROUP BY f.finca, d.anyo
ORDER by f.finca, d.anyo

91 Records. Records and totals OK

There is a property that only has expenses

If one the two consultations and filter per year

Code: Select all

SELECT f.finca Property, l.anyo Year,  sum(l.total) Income, sum(d.total) Expenses
FROM Finques f
LEFT JOIN Lloguers l ON l.fincaID = f.fincaID AND l.anyo = d.anyo
LEFT JOIN Despeses d ON f.fincaID = d.fincaID AND l.anyo = d.anyo
GROUP BY f.finca, l.anyo
ORDER by f.finca, l.anyo

Only 83 recods.

The revenue totals are multiplied by the number of expense records and vice versa.

Thanks in advance

Carles

[capellan]

Hi Carles,

Carles wrote:

There is a property that only has expenses

I noticed that there are 91 expenses records but only
83 income records.

I was looking in Google for the phrase:
"mysql left join multiplying results" and
there are too many results.

For example:
https://stackoverflow.com/questions/707 ... each-other
https://stackoverflow.com/questions/261 ... the-result

Al

[cbarbal]

Hi Al,

You are a crack. I had only searched to join three tables in SQLite.

After visiting several web pages, in some they say that you can not do with three tables, and looking only to have sql compatible with SQLIte I have found the solution.

I put the formula in case it might be of interest to someone.

Code: Select all

SELECT finca Property, d.anyo Year,  l.Income , d.Expenses
FROM Finques f
LEFT JOIN (
	SELECT fincaID, anyo ,  sum(total) Expenses
	FROM Despeses
	GROUP BY fincaID, anyo) d
	ON f.fincaID = d.fincaID AND l.anyo = d.anyo
LEFT JOIN (
	SELECT fincaID, anyo, sum(total) Income
	FROM Lloguers
	GROUP BY fincaID, anyo ) l
	ON l.fincaID = f.fincaID AND l.anyo = d.anyo

Now I can only adapt it in LiveCode, for each property

Thanks forum,

Carles

[bogs]

Hi Al,
You are a crack.

I hope (in some way) that this is a compliment simply not completed, like "you are a crack shot", instead of something negative which is how it looks at first blush.

[cbarbal]

Hi bogs,

I already said the first time that my English is Google's, if someone was to feel offended I apologize. Many times what I translate, I return to Spanish and it's not what I want to put ...

Messi is considered a "crack" of football, Alejandro is a "crack" of Google for finding the phrase that has allowed me to solve my problem.

I hope that with this clarification, if I put any question in the forum, I will continue to answer.

Regards,

Carles

[bogs]

I already said the first time that my English is Google's, if someone was to feel offended I apologize. Many times what I translate, I return to Spanish and it's not what I want to put ...

I was guessing that was the case, and just wanted to make sure before there were any hard feelings, since text (even among people writing/reading in the same language) is a lot harder to parse than the spoken word, which gets misunderstood easily enough itself

Thank you for the clarification.

[capellan]

Hi Carles,

Thanks for the compliment!
The first time that I heard the word Crack as a compliment
was from a sports commentator talking about Leonel Messi.

Finding the correct words for google searching, sometimes requires
a lot of lateral thinking. That is, sometimes you have to search for
something similar to what you are really looking for...

I answered your email before reading your answer in
this thread, so many thanks again for posting
a solution in this forum.

Al