Report from different SQLite Table

My SQLite Database is build as follow.

Table:
CREATE Client (ID INTEGER PRIMARY KEY, ClientNr INTEGER, Client TEXT, Adres TEXT,Zip TEXT,etc………………
CREATE Order (ID INTEGER PRIMARY KEY, ClientNr INTEGER, OrderNr INTEGER, etc……………
CREATE Part (OrderNr INTEGER, PartNr INTEGER, etc…………….

I like to have a report with, a list from every Client with the orders and the parts.

I used the SQL code:

SQL=“SELECT * FROM Client a, Order b, Part c WHERE a.ClientNr = “ + ClientNr +” = AND b.ClientNr AND b.OrderNr = c.OrderNr ;"

But I Receive to match Parts as necessary, also parts rom other orders.
Wath is Wrong?

You need to JOIN the tables. See SQL Joins. There are different kind of joins, you’ll most probably want to LEFT JOIN Client with Order and LEFT JOIN Order with Part (SQL Left Join.

These tables are already joined in the query.
(The joins are assumed as INNER joins when you use this syntax.)

But there is one condition missing
SQL=“SELECT * FROM Client a, Order b, Part c WHERE a.ClientNr = “ + ClientNr +” = AND b.ClientNr = a.ClientNr AND b.OrderNr = c.OrderNr ;"

I can see some problems with your table definitions.
Firstly, you have both an ID and a ClientNr in the Client table.
Thats fine, but the value you hold in the ORDER table should be the ID from the Client Table. This never changes, but the ClientNr might. And if it did, the Orders would be ‘lost’

The Part table doesnt need to hold Client, because that is an attribute of Order.

I think the Order Table should be:

CREATE Order (ID INTEGER PRIMARY KEY, ClientID INTEGER, OrderNr INTEGER, etc……………

and the Part table should be

CREATE Part (ID INTEGER PRIMARY KEY,PartNr as integer, OrderID INTEGER, etc……………

Next, not all customers have orders, and not all orders have parts.

This is where the OUTER join syntax is helpful.

Using the tables above , the query becomes

"Select Client.client,OrderNr,PartNr (etc) from Client LEFT OUTER JOIN Order on Order.ClientID = Client.ID LEFT OUTER JOIN Part on OrderID = Order.ID where Client.ClientNr = " + ClientNr + ";"

LEFT OUTER JOIN is the same as LEFT JOIN.

This is true…

but the main problem with the original query is the lack of joining at all caused by
b.ClientNr = a.ClientNr being missing.

An inner join query like this would return only customers who have orders that have parts.
That may actually be all that is required.
In which case

"Select Client.client,OrderNr,PartNr (etc) from Client, Order,Part where Order.ClientID = Client.ID and OrderID = Order.ID and Client.ClientNr = " + ClientNr + ";"

you’re missing at least 2 single quotes ’ if ClientNr is a string
or you miss a str(ClientNr) if CLientNr is an integer.

SQL="SELECT * FROM Client a, Order b, Part c WHERE a.ClientNr = '“ + ClientNr +"' AND b.ClientNr AND b.OrderNr = c.OrderNr ;"

I have tray it on different ways to build in the Statements INNER JOIN and OUTER LEFT JOIN, without results.
Following the original SQL CREATE and SELECT orders.
But where comes the JOINT order?

The result now is: Every “Bestelling” has one “Dorpel” and one “Vak”.
But a “Bestelling” can be have more “Dorpels” and a “Dorpel” can be have more “Vak”

[code]SQL =“CREATE TABLE Bestelling (ID INTEGER PRIMARY KEY, KltNum INTEGER”+_
“,PrijsID INTEGER, OrdNumKlt TEXT, User TEXT, OrdNumVT INTEGER,”+_
“ BestDat TEXT, Week INTEGER ,Jaar INTEGER, Status INTEGER”+_
“, StatDate TEXT,Systeem TEXT, SysMaat INTEGER, Kleur TEXT”+_
“,VspBiBr INTEGER, VspBuBr INTEGER,VspBiDpt INTEGER, “+_
“ VspBuDpt INTEGER, Kop INTEGER, Kap INTEGER, Maat1 INTEGER”+_
“,Maat2 INTEGER, Maat3 INTEGER, Diepte INTEGER, BoorPat TEXT, Notitie TEXT)”

SQL=“CREATE TABLE Dorpel (Bestelling INTEGER, Dorpel INTEGER, Merk TEXT”+_
“, Aantal INTEGER ,TotL INTEGER, SlA INTEGER, SlB INTEGER, SlType INTEGER”+_
“, KopLs TEXT, KopRs TEXT, VrMaLs INTEGER, VrMaRs INTEGER”+_
“, NeuVerLs INTEGER, NeuVerRs INTEGER, AantVak INTEGER, Status INTEGER)”

SQL=“CREATE TABLE Vak(Bestelling INTEGER, Aantal INTEGER, Dorpel INTEGER”+_
“, Vak INTEGER, NB1 INTEGER, Sponning TEXT, SponNr INTEGER”+_
“, Vsp TEXT, DagMaat INTEGER, Kap TEXT, Letter TEXT, LengteK INTEGER”+_
“, Strip TEXT, LengteS INTEGER, StatusK INTEGER, StatusS INTEGER, NB2 INTEGER)”

SQL=“SELECT * FROM Bestelling, Dorpel, Vak WHERE Bestelling.OrdNumVT =”+ KlantBox.Cell(SelRow,0)+" AND Bestelling.ID = Dorpel.Bestelling = Vak.Bestelling ;"[/code]

SQL="SELECT * FROM Bestelling, Dorpel, Vak WHERE Bestelling.OrdNumVT ='"+ KlantBox.Cell(SelRow,0)+"' AND Bestelling.ID = Dorpel.Bestelling AND Dorpel.Bestelling = Vak.Bestelling"

Now i have the next problem:
All the “Vak” are printed but…
The 1. “Dorpel” hase 1 “Vak”
The 2. “Dorpel” hase 2 “Vak”
But by the 2. “Dorpel” the pinrt is asfollows:
Dorpel2 date’s…
Val1 (from Dorpel2) date’s
Dorpel2 date’s…
Val1 (from Dorpel2) date’s
Val2 (from Dorpel2) date’s
If the dorpe hase 5 vak:
1 time teh print is with 1. Vak the 2. print with 1. & 2. Vak, the 3. print with 1.,2. & 3. Vak ect.