Android Question Sqlite Join multiple tables

[jpvniekerk]

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.

Any help will be GREATLY appreciated!

 

[DonManfred]

like this?

    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 **

 

[sorex]

like this?

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

 

[jpvniekerk]

@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.

Johannes

 

[sorex]

yeah, I just fixed it in the post above

didn't notice the b_id, c_id & d_id

 

[DonManfred]

@manfred!!!! You're a star!

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

Klick on like if a answer is helpful for you

 

[jpvniekerk]

@DonManfred thanks for the correction - slip of the pen (or rather fingers!). Hope I've got it right this time.