(I did not read the previous 2 posts yet)
Since you want to process customers independently of wether they have orders, a
LEFT JOINT could be what you want.
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
And you can use a map with ids as keys to handle the duplicate customer data:
(And I would not trust a column OrderCount if I see it
)
Code: Select all
UseSQLiteDatabase()
DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
DatabaseUpdate(DB, "CREATE TABLE People (Id INTEGER, Name TEXT, Address TEXT, OrderCount INTEGER)")
DatabaseUpdate(DB, "INSERT INTO People VALUES (1, 'A', 'First Road', 1)")
DatabaseUpdate(DB, "INSERT INTO People VALUES (2, 'B', 'First Avenue', 2)")
DatabaseUpdate(DB, "INSERT INTO People VALUES (3, 'C', 'street', 0)")
DatabaseUpdate(DB, "INSERT INTO People VALUES (4, 'D', 'Second Avenue', 3)")
DatabaseUpdate(DB, "CREATE TABLE Orders (Id INTEGER, UserId INTEGER, OrderDetails TEXT)")
DatabaseUpdate(DB, "INSERT INTO Orders VALUES (1, 2, 'Toast_B')")
DatabaseUpdate(DB, "INSERT INTO Orders VALUES (2, 2, 'Bread_B')")
DatabaseUpdate(DB, "INSERT INTO Orders VALUES (3, 1, 'Bread_A')")
DatabaseUpdate(DB, "INSERT INTO Orders VALUES (4, 1, 'Marmalade_A')")
DatabaseUpdate(DB, "INSERT INTO Orders VALUES (5, 4, 'Chocolate_D')")
Structure Order
Id.i
UserId.i
OrderDetails.s
EndStructure
Structure customer
Id.i
Name.s
Address.s
OrderCount.i
oCnt.i
Map Orders.Order()
EndStructure
NewMap Customers.Customer()
SQL$ = ""
SQL$ + "SELECT "
SQL$ + "p.Id As p_Id, Name, Address, OrderCount, "
SQL$ + "od.Id As od_Id, UserId, OrderDetails "
SQL$ + "FROM People As p LEFT JOIN Orders As od "
SQL$ + "ON p.Id = od.UserId "
SQL$ + "ORDER BY p.Id, od.Id;"
If DatabaseQuery(DB, SQL$)
While NextDatabaseRow(DB)
Debug GetDatabaseString(DB, DatabaseColumnIndex(DB, "Name")) + ": " + GetDatabaseString(DB, DatabaseColumnIndex(DB, "OrderDetails"))
p_Id$ = GetDatabaseString(DB, DatabaseColumnIndex(DB, "p_id"))
p_Id = Val(p_Id$)
Customers(p_Id$)\Id = p_Id
Customers(p_Id$)\Name = GetDatabaseString(DB, DatabaseColumnIndex(DB, "Name"))
Customers(p_Id$)\Address = GetDatabaseString(DB, DatabaseColumnIndex(DB, "Address"))
Customers(p_Id$)\OrderCount = Val(GetDatabaseString(DB, DatabaseColumnIndex(DB, "OrderCount")))
od_Id$ = GetDatabaseString(DB, DatabaseColumnIndex(DB, "od_Id"))
od_Id = Val(od_Id$)
If od_Id$
Customers(p_Id$)\Orders(od_Id$)\Id = od_Id
Customers(p_Id$)\Orders(od_Id$)\UserId = Val(GetDatabaseString(DB, DatabaseColumnIndex(DB, "UserId")))
Customers(p_Id$)\Orders(od_Id$)\OrderDetails = GetDatabaseString(DB, DatabaseColumnIndex(DB, "OrderDetails"))
EndIf
Wend
Debug "----"
ForEach Customers()
Customers()\oCnt = MapSize(Customers()\Orders())
Debug Customers()\Name + "(" + Customers()\oCnt + "/" + Customers()\OrderCount + ")"
;do some work here
If Customers()\oCnt > 0
ForEach Customers()\Orders()
Debug " " + Customers()\Orders()\OrderDetails
;do some work with those data
Next
Else
Debug " [no orders]"
EndIf
Next
Debug "----"
Else
Debug SQL$
Debug DatabaseError()
EndIf
CloseDatabase(DB)
EndIf