Android Question (Solved) SQL Query Causing Headache

[omo]

From the diagram below, i have two tables A and B. I have access to year and myid to select each record at a time. If i select record 1 in Table A, i will select its corresponding record 1 in Table B based on year and myid. If i select record 2 in Table A, i will select its corresponding record 2 in Table B based on year and myid; etc. which will finally look like Table C output below. Maybe using DISTINCT and INNER JOIN statements or otherwise you seem correct.

My question is: How can i compose SQL Query to select each record based on year and myid without repeating record or selecting more than one record at a time?

NOTE: This below is my attempt, in my real application; Table A are B represent variable names for multiple databases in my own query. "year" doesn't seem to work in my composed query, it makes selection only based on "myid" which selects only last '1' in myid; it makes my query to be wrong.

Dim rs As ResultSet = B4XPages.MainPage.sql1.ExecQuery("SELECT DISTINCT * FROM " & Table A & " INNER JOIN " & Table B & " WHERE " & Table A & ".year = " & Table B & ".year AND " & Table A & ".myid = " & Table B & ".myid AND " & Table B & ".myid =" & B4XPages.MainPage.ppq2.rowidanswt & " GROUP BY " & Table A & ".myid")
'

 

[Mahares]

How can i compose SQL Query to select each record based on year and myid without repeating record

Use UNION ALL. Something like this:

Dim strQuery As String = $"
    SELECT DISTINCT * FROM A
    UNION ALL
    SELECT DISTINCT * FROM B ORDER BY YEAR, myid
    "$
    Dim rs As ResultSet =SQL1.ExecQuery(strQuery)
    Do While rs.NextRow   
        LogColor($"${rs.getint2(0)}${TAB}${rs.getstring2(1)}${TAB}${rs.getstring2(2)} ${TAB}${rs.getstring2(3)} ${TAB}${rs.getstring2(4)} ${TAB}${rs.getstring2(5)}"$, xui.Color_Blue)
    Loop

 

[omo]

Use UNION ALL. Something like this:

Dim strQuery As String = $"
    SELECT DISTINCT * FROM A
    UNION ALL
    SELECT DISTINCT * FROM B ORDER BY YEAR, myid
    "$
    Dim rs As ResultSet =SQL1.ExecQuery(strQuery)
    Do While rs.NextRow
        LogColor($"${rs.getint2(0)}${TAB}${rs.getstring2(1)}${TAB}${rs.getstring2(2)} ${TAB}${rs.getstring2(3)} ${TAB}${rs.getstring2(4)} ${TAB}${rs.getstring2(5)}"$, xui.Color_Blue)
    Loop

Thank you very much @Mahares, I will work on it later tomorrow by God's grace. We are in our midnight at moment. However, like I have in my question, what of if I want to include WHERE clause into your composed query i.e

... WHERE myid = kid AND year = yr ;
Please, help me include WHERE clause so I can test it together.
For now, this is my attempt:

Dim strQuery As String = $" SELECT DISTINCT * FROM A UNION ALL SELECT DISTINCT * FROM B WHERE myid = kid AND year = yr ORDER BY YEAR, myid "$

If you think otherwise, help me recompose so I can try it together at once. My aim is to select only one record from Table A and its corresponding record in B based on variable name stored in kid and yr from previous b4xpage at a time
Thank you so much!

 

[Mahares]

Please, help me include WHERE clause so I can test it together

Here it is with the WHERE clause:

Dim kid As Int = 2
    Dim yr As Int = 2015
    Dim strQuery As String = $"
    SELECT DISTINCT * FROM A 
    WHERE myid = ${kid}  AND year = ${yr}
    UNION ALL
    SELECT DISTINCT * FROM B 
    WHERE myid = ${kid}  AND year = ${yr}
    ORDER BY YEAR, myid
    "$
    Dim rs As ResultSet =SQL1.ExecQuery(strQuery)
    Do While rs.NextRow    
        LogColor($"${rs.getint2(0)}${TAB}${rs.getstring2(1)}${TAB}${rs.getstring2(2)} ${TAB}${rs.getstring2(3)} ${TAB}${rs.getstring2(4)} ${TAB}${rs.getstring2(5)}"$, xui.Color_Blue)
    Loop    
    rs.Close

 

[Mahares]

Here is another comparable way to the code in my previous post without using DISTINCT or ALL. It does the same thing. When you use UNION instead of UNION ALL, it eliminates the duplicates., therefore, no need for DISTINCT.

Dim kid As Int = 2
    Dim yr As Int = 2015
    Dim strQuery As String = $"
    SELECT  * FROM A 
    WHERE myid = ${kid}  AND year = ${yr}
    UNION 
    SELECT  * FROM B 
    WHERE myid = ${kid}  AND year = ${yr}
    ORDER BY YEAR, myid
    "$
    Dim rs As ResultSet =SQL1.ExecQuery(strQuery)
    Do While rs.NextRow    
        LogColor($"${rs.getint2(0)}${TAB}${rs.getstring2(1)}${TAB}${rs.getstring2(2)} ${TAB}${rs.getstring2(3)} ${TAB}${rs.getstring2(4)} ${TAB}${rs.getstring2(5)}"$, xui.Color_Blue)
    Loop    
    rs.Close

 

[omo]

Apology for my late response @Mahares, it was never in my intentional habit. There are too many things demanding my little time especially being Monday first working day in my country. I have close to 100 tables in my database where i was trying to apply the concept having found out fault in my own composed SQL. Initially, i wasted needless time trying to integrate it because it never ran pass 'strQuery', but output test showed all variables were correctly substituted. From Dim rs As ResultSet =SQL1.ExecQuery(strQuery), it didn't run before found out , i forgot to single quote this:'${yr}' because i use string in the main application.

Your two solutions worked based on the question i asked above, but i found out it was not exactly what i needed for my present application. I didn't explain very well enough i guess. Your two solutions retrieve each record from Table A and its equivalent in Table B, then make both available i Table A and Table B in my application is not exactly the same. But i found out what i needed was for the results to be made available with their table structures. I solved it with your help by restructuring it like this:
Dim strQuery As String = $"Select DISTINCT * FROM A,B WHERE (A.myid = ${kid} And A.yearpq = ${yr}) AND (B.myid = ${kid} And B.yearpq = ${yr})
ORDER BY myid,yearpq "$

However, i will mark your code as solution for this question asked on this thread. Thank you once again @Mahares and bare with me for late response , it was never intentional. Regards

 

[Mahares]

Dim strQuery As String = $"Select DISTINCT * FROM A,B WHERE (A.myid = ${kid} And A.yearpq = ${yr}) AND (B.myid = ${kid} And B.yearpq = ${yr})
ORDER BY myid,yearpq "$

In that case it would be safer to use a parameterized query like this:

Dim strQuery As String = $"SELECT DISTINCT * FROM A,B
    WHERE (A.myid = ? And A.yearpq = ?)
     AND (B.myid = ? And B.yearpq = ?)
    ORDER BY myid,year"$
Dim rs As ResultSet =SQL1.ExecQuery2(strQuery, Array As String( kid, yr, kid, yr ))

 

[omo]

In that case it would be safer to use a parameterized query like this:

Dim strQuery As String = $"SELECT DISTINCT * FROM A,B
    WHERE (A.myid = ? And A.yearpq = ?)
     AND (B.myid = ? And B.yearpq = ?)
    ORDER BY myid,year"$
Dim rs As ResultSet =SQL1.ExecQuery2(strQuery, Array As String( kid, yr, kid, yr ))

Ok, thanks; Noted