CODEWITHSUNDEEP
androidsqlsqlite
No_Name_Code
No_Name_Code

Reputation: 299

How to get data from two table in sqlite in android

I am unable to getting data from two table in android using left join.

Here is the my query:

public Cursor getrespondentDataNew(String loc_id) {
        Cursor cur = null;

final String MY_QUERY = "SELECT r.name, r.site_name, r.age, r.gender, rt.rtype_name, r.parent_code, r.code, r.site_code, r.interview_date FROM respondentTable r INNER JOIN respondentType rt ON r.type_code = rt.rtype_code WHERE r.site_code='" + loc_id + "'";

        cur = db.rawQuery(MY_QUERY, null);
 Log.i("sql:", "SQL Test=" + MY_QUERY);
 Log.i("cur:", "cur Test=" + cur.getCount());

and 

final String MY_QUERY = "SELECT r.name, r.site_name, r.age, r.gender, rt.rtype_name, r.parent_code, r.code, r.site_code, r.interview_date FROM respondentTable r INNER JOIN respondentType rt ON r.type_code = rt.rtype_code WHERE r.site_code=?";
cur = db.rawQuery(MY_QUERY, new String[]{loc_id});

....

04-04 11:45:28.212: I/sql:(11017): SQL Test=SELECT r.name, r.site_name, r.age, r.gender, rt.rtype_name, r.parent_code, r.code, r.site_code, r.interview_date FROM respondentTable r,respondentType rt where r.type_code = rt.rtype_code and r.site_code='4'
04-04 11:45:28.224: I/cur:(11017): cur Test=0

EDIT: If i run this Query: why i am getting last value of rt.rtype_name:

SELECT r.name, r.site_name, r.age, r.gender, rt.rtype_name, r.parent_code, r.code, r.site_code, r.interview_date FROM respondentTable r,respondentType rt where r.type_code = rt.rtype_code   or r.site_code='4' group by  r.code

In both case i did not get value, what is the wrong with this query.

My tables:

respondentTable enter image description here

respondentType

enter image description here

I am testing site_code 4.

Upvotes: 0

Views: 4202

Answers (2)

No_Name_Code
No_Name_Code

Reputation: 299

I Solve Problem using left outer join.

SELECT r.name, r.site_name, r.age, r.gender, rt.rtype_name, r.parent_code, r.code, r.site_code, r.interview_date FROM respondentTable r LEFT OUTER JOIN respondentType rt ON r.type_code = rt.rtype_code    where r.site_code='4'

Upvotes: 0

Lucifer
Lucifer

Reputation: 29670

You can try it another way by following query style, 

final String MY_QUERY = "SELECT r.name, r.site_name, r.age, r.gender, rt.rtype_name, r.parent_code, r.code, r.site_code, r.interview_date FROM respondentTable r,respondentType rt where r.type_code = rt.rtype_code and r.site_code='" + loc_id + "'";

Upvotes: 2

Related Questions