I have a database with several tables that is linked to the main table with ID fields, and is kind of "lookup tables". I'm trying to get a query that will eliminate a lot of programming, but I am just not getting anywhere.
This is a simplified version of the database schematic and the resulting query I want.
I have tried tons of variations with JOIN, UNION, WHERE, etc, but I just cannot get it right - though it seems like it should be very simple. I am learning SQL, but this one seems above my capability!
I'm attaching the above sqlite database for anyone willing to help.
Just a note, my actual database has more than 10 of these "lookup" fields and hundreds of records, so creating the result table will be possible through programming, but it will be very cumbersome! I'm hoping to get a more efficient solution with SQL.
SQL1.Initialize(File.DirDefaultExternal, "testquery.db", True)
Dim c As Cursor
c = SQL1.ExecQuery($"SELECT A.id AS Aid, A.Descr AS Descr, A.Notes as Notes, B.Bname, C.Cname, D.Dname, D.Dvalue FROM tblA A
LEFT JOIN tblB B ON B.id = A.B_ID
LEFT JOIN tblC C ON C.id = A.C_ID
LEFT JOIN tblD D ON D.id = A.D_ID
"$)
For i = 0 To c.RowCount-1
c.Position = i
Log($"Result ${i}: ${c.GetInt("Aid")},${c.GetString("Descr")},${c.GetString("Bname")},${c.GetString("Cname")},${c.GetString("Dname")}, ${c.GetString("Dvalue")}, ${c.GetString("Notes")}"$)
Next
Next
** Activity (main) Create, isFirst = true **
Result 0: 1,Descr 1,B1,C1,D1, 10, Notes1
Result 1: 2,Descr 2,B2,null,D2, 20, Notes 2
Result 2: 3,Descr 3,null,null,D1, 10, Descr 3
Result 3: 4,Descr 4,B1,C2,null, null, Notes 4
Result 4: 5,Descr 5,null,null,null, null, Notes 5
** Activity (main) Resume **
SELECT distinct(descr),bname,cname,dname,dvalue,notes FROM tbla left join tblb on tblb.id=tbla.b_id left join tblc on tblc.id=tbla.c_id left join tbld on tbld.id=tbla.d_id
@manfred!!!! You're a star! Thanks - your statement works perfectly! @sorex. Thanks, but it doesn't give the correct results. it seems the fields are all mixed up in the query.
I am NOT the User manfred... My Name is DonManfred (if you want to mark me @DonManfred). My realname is Manfred, that´s correct.
But you did mark another user