[SOLVED] Right Join and Outer join in SQLite

[madref]

This query i ripped straight out of my MS Access Database

Code: SQL  [Select][+][-]

1. 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_ID
2. FROM 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_ID
3. ORDER 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]

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]

The joins are now done.
But now this:

Code: SQL  [Select][+][-]

1. [LID_achternaam] & IIf(NOT IsNull([LID_voornaam]),", " &
2. [LID_voornaam] & IIf(NOT IsNull([LID_tussenvoegsel])," " &
3. [LID_tussenvoegsel]), IIf(NOT IsNull([LID_tussenvoegsel]),", " &
4. [LID_tussenvoegsel])) & IIf(IsNull([bet_omschrijving]),""," [" &
5. [bet_omschrijving] & "]") AS Omschrijving

[madref]

Made it
Final view looks like:

Code: SQL  [Select][+][-]

1. SELECT Bet_ID, StrFTime("%d-%m-%Y",Bet_Datum) AS Datum, printf("€ %.2f",Bet_Bedrag)  AS Bedrag,
2. Cat_Omschrijving AS Categorie, SUB_Omschrijving AS Subcategorie,
3. CASE WHEN Bet_Omschrijving <> "" THEN REPLACE(RTRIM(COALESCE(Lid_Achternaam || ", ", "") ||
4. COALESCE(LID_Voornaam || " ", "") || COALESCE(LID_tussenvoegsel, "")), "  ", " ") || " [" ||
5. Bet_omschrijving || "] " ELSE REPLACE(RTRIM(COALESCE(Lid_Achternaam || ", ", "") ||
6. COALESCE(LID_Voornaam || " ", "") || COALESCE(LID_tussenvoegsel, "")), "  ", " ") END AS Omschrijving,
7. Bet_Betaalmiddel AS Betaalmiddel, Bet_Bedrag, Sei_ID AS Seizoen,
8. Bet_Afschriftnummer AS Afschrift, Lid_ID, Bet_Datum
9. FROM tbl_Personen INNER JOIN (tbl_Subcategorie JOIN (tbl_Seizoenen INNER JOIN
10. (tbl_Betalingen LEFT JOIN tbl_Categorie ON tbl_Betalingen.Bet_Categorie = tbl_Categorie.Cat_ID)
11. ON tbl_Seizoenen.Sei_ID = tbl_Betalingen.Bet_Boek_Sei_ID) ON
12. tbl_Subcategorie.SUB_ID = tbl_Betalingen.Bet_Subcategorie) ON
13. tbl_Personen.Lid_ID = tbl_Betalingen.Bet_Lid_ID
14. ORDER BY Bet_Datum DESC, 3, 4, 6, 8