[SOLVED] Right Join and Outer join in SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2016-11-13 08:53

This query i ripped straight out of my MS Access Database
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
tbl_Betalingen.Bet_ID, tbl_Betalingen.Bet_Datum AS Datum,
FormatCurrency([Bet_Bedrag]) AS Bedrag, tbl_Categorie.Cat_Omschrijving
AS Categorie, tbl_Subcategorie.SUB_Omschrijving AS Subcategorie,
[LID_achternaam] & IIf(NOT IsNull([LID_voornaam]),", " &
[LID_voornaam] & IIf(NOT IsNull([LID_tussenvoegsel])," " &
[LID_tussenvoegsel]),IIf(NOT IsNull([LID_tussenvoegsel]),", " &
[LID_tussenvoegsel])) & IIf(IsNull([bet_omschrijving]),""," [" &
[bet_omschrijving] & "]") AS Omschrijving,
tbl_Betalingen.Bet_Betaalmiddel AS Betaalmiddel,
tbl_Betalingen.Bet_Bedrag, qry_Seizoenen.Sei_ID AS Seizoen,
tbl_Betalingen.Bet_Afschriftnummer AS Afschrift,
tbl_Personen.Lid_IDFROM tbl_Personen INNER JOIN (tbl_Subcategorie
RIGHT JOIN (qry_Seizoenen INNER JOIN (tbl_Betalingen LEFT JOIN
tbl_Categorie ON tbl_Betalingen.Bet_Categorie = tbl_Categorie.Cat_ID)
ON qry_Seizoenen.Sei_ID = tbl_Betalingen.Bet_Boek_Sei_ID) ON
tbl_Subcategorie.SUB_ID = tbl_Betalingen.Bet_Subcategorie) ON
tbl_Personen.Lid_ID = tbl_Betalingen.Bet_Lid_IDORDER BY
tbl_Betalingen.Bet_Datum DESC , FormatCurrency([Bet_Bedrag]),
tbl_Categorie.Cat_Omschrijving, [LID_achternaam] & IIf(NOT
IsNull([LID_voornaam]),", " & [LID_voornaam] & IIf(NOT
IsNull([LID_tussenvoegsel])," " & [LID_tussenvoegsel]),IIf(NOT
IsNull([LID_tussenvoegsel]),", " & [LID_tussenvoegsel])) &
IIf(IsNull([bet_omschrijving]),""," [" & [bet_omschrijving] & "]"),
tbl_Betalingen.Bet_Bedrag;And i wan to re-write it to SQLite.
But SQLite doesn't know right join and outer join.
Can anyone help me re-write it to SQLite?

----------------------------------------------------------------------
Thaddy · 2016-11-13 09:50

https://www.sqlite.org/omitted.html

For an example to convert the missing see
http://stackoverflow.com/questions/9147025/sqlite3-simulate-right-
outer-join-with-left-joins-and-union
and
http://www.sqlitetutorial.net/sqlite-full-outer-join/

----------------------------------------------------------------------
madref · 2016-11-13 18:30

The joins are now done.
But now this:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
[LID_achternaam] & IIf(NOT IsNull([LID_voornaam]),", " &
[LID_voornaam] & IIf(NOT IsNull([LID_tussenvoegsel])," " &
[LID_tussenvoegsel]), IIf(NOT IsNull([LID_tussenvoegsel]),", " &
[LID_tussenvoegsel])) & IIf(IsNull([bet_omschrijving]),""," [" &
[bet_omschrijving] & "]") AS Omschrijving

----------------------------------------------------------------------
madref · 2016-11-13 23:09

Made it
Final view looks like:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
Bet_ID, StrFTime("%d-%m-%Y",Bet_Datum) AS Datum, printf("€
%.2f",Bet_Bedrag)  AS Bedrag, Cat_Omschrijving AS Categorie,
SUB_Omschrijving AS Subcategorie, CASE WHEN Bet_Omschrijving <> ""
THEN REPLACE(RTRIM(COALESCE(Lid_Achternaam || ", ", "") ||
COALESCE(LID_Voornaam || " ", "") || COALESCE(LID_tussenvoegsel, "")),
"  ", " ") || " [" || Bet_omschrijving || "] " ELSE
REPLACE(RTRIM(COALESCE(Lid_Achternaam || ", ", "") ||
COALESCE(LID_Voornaam || " ", "") || COALESCE(LID_tussenvoegsel, "")),
"  ", " ") END AS Omschrijving, Bet_Betaalmiddel AS Betaalmiddel,
Bet_Bedrag, Sei_ID AS Seizoen, Bet_Afschriftnummer AS Afschrift,
Lid_ID, Bet_Datum FROM tbl_Personen INNER JOIN (tbl_Subcategorie JOIN
(tbl_Seizoenen INNER JOIN (tbl_Betalingen LEFT JOIN tbl_Categorie ON
tbl_Betalingen.Bet_Categorie = tbl_Categorie.Cat_ID) ON
tbl_Seizoenen.Sei_ID = tbl_Betalingen.Bet_Boek_Sei_ID) ON
tbl_Subcategorie.SUB_ID = tbl_Betalingen.Bet_Subcategorie) ON
tbl_Personen.Lid_ID = tbl_Betalingen.Bet_Lid_ID ORDER BY Bet_Datum
DESC, 3, 4, 6, 8