SQLite JOIN Problem.

General topics about the Android-Platform itself.
Coding issues please to the subforum right below.

SQLite JOIN Problem.

Postby plusminus » Wed Feb 13, 2008 6:34 pm

Hey guys,

I'm trying to do an INNER JOIN on two SQLite Tables but somehow it does not work.
there are two Tables with 1 datarow that I ensured to exist:
t_mods
ID(INTEGER, PRIMARY KEY), Name(VARCHAR)
0, "plusminus"

t_boards
ID(INTEGER, PRIMARY KEY), ForumName(VARCHAR), MOD_ID(INTEGER)
0, "bla", 0

This is my Query:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. Cursor c = myDB.query("SELECT Name,ForumName"
  2.         + " FROM " + TABLE_MODERATORS + " INNER JOIN " + TABLE_BOARDS
  3.         + " ON " + TABLE_MODERATORS + ".ID = " + TABLE_BOARDS + ".MOD_ID;",
  4.         null);
Parsed in 0.031 seconds, using GeSHi 1.0.8.4


c becomes != null and Debugger runs smooth over it and the cursor provides the "compiled" statement:
Syntax: [ Download ] [ Hide ]
Using sql Syntax Highlighting
  1. SELECT Name FROM t_mods INNER JOIN t_boards ON t_mods.ID = t_boards.MOD_ID
Parsed in 0.008 seconds, using GeSHi 1.0.8.4


I can also retrieve the indixes of both columns (which came from the different tables!):
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. int modNameColumn = c.getColumnIndex("Name");
  2. int forumNameColumn = c.getColumnIndex("ForumName");
Parsed in 0.031 seconds, using GeSHi 1.0.8.4

Everything runs fine until here :!:

But the result contains ZERO joined datasets. :shock:
But it should contain the one I wrote in the first lines...!

I've never done SQLite before (just mySQL) and I'm pretty sure my code is correct. Is there perhaps a kind of SQLite specialty I don't know :?:

Regards,
plusminus
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Top

Postby Peli » Thu Mar 06, 2008 8:45 am

Have you tried to have a look at the database directly?

Code: Select all
adb shell
# cd data/data/...../databases
# sqlite3 plusminustable.db
sqlite> .tables
sqlite> .dump t_mods
sqlite> .dump t_boards
sqlite> SELECT Name FROM t_mods INNER JOIN t_boards ON t_mods.ID = t_boards.MOD_ID;


The inner join worked for me there, but I don't know exactly how your table looked like (I used inner join on my shopping tables). So let me know if you already have a problem within sqlite3.
Don't forget the trailing ";"!

Peli
Peli
Junior Developer
Junior Developer
 
Posts: 17
Joined: Thu Dec 27, 2007 10:54 am

Postby smithms112 » Wed Dec 24, 2008 9:23 pm

is there any way to use the JOIN thing with Content Providers??
Smith,
smithms112
Freshman
Freshman
 
Posts: 2
Joined: Fri Oct 31, 2008 10:27 am

Postby kolch9 » Fri Jun 26, 2009 5:32 pm

plusminus have you found a solution?because i need to delete the rows of two tables, and i need to join them before to do it..please help me!!!thanks!!!!

stefano
kolch9
Junior Developer
Junior Developer
 
Posts: 11
Joined: Wed Feb 18, 2009 2:17 pm

Postby eugeniuskr » Thu Aug 13, 2009 9:57 am

smithms112 wrote:is there any way to use the JOIN thing with Content Providers??

+1

i need an answer.
eugeniuskr
Freshman
Freshman
 
Posts: 9
Joined: Mon Jun 22, 2009 12:27 pm

Re: SQLite JOIN Problem.

Postby saturne2008 » Wed Apr 07, 2010 11:48 am

plusminus wrote:Hey guys,

I'm trying to do an INNER JOIN on two SQLite Tables but somehow it does not work.
there are two Tables with 1 datarow that I ensured to exist:
t_mods
ID(INTEGER, PRIMARY KEY), Name(VARCHAR)
0, "plusminus"

t_boards
ID(INTEGER, PRIMARY KEY), ForumName(VARCHAR), MOD_ID(INTEGER)
0, "bla", 0

This is my Query:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. Cursor c = myDB.query("SELECT Name,ForumName"
  2.         + " FROM " + TABLE_MODERATORS + " INNER JOIN " + TABLE_BOARDS
  3.         + " ON " + TABLE_MODERATORS + ".ID = " + TABLE_BOARDS + ".MOD_ID;",
  4.         null);
Parsed in 0.035 seconds, using GeSHi 1.0.8.4


c becomes != null and Debugger runs smooth over it and the cursor provides the "compiled" statement:
Syntax: [ Download ] [ Hide ]
Using sql Syntax Highlighting
  1. SELECT Name FROM t_mods INNER JOIN t_boards ON t_mods.ID = t_boards.MOD_ID
Parsed in 0.002 seconds, using GeSHi 1.0.8.4


I can also retrieve the indixes of both columns (which came from the different tables!):
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. int modNameColumn = c.getColumnIndex("Name");
  2. int forumNameColumn = c.getColumnIndex("ForumName");
Parsed in 0.035 seconds, using GeSHi 1.0.8.4

Everything runs fine until here :!:

But the result contains ZERO joined datasets. :shock:
But it should contain the one I wrote in the first lines...!

I've never done SQLite before (just mySQL) and I'm pretty sure my code is correct. Is there perhaps a kind of SQLite specialty I don't know :?:

Regards,
plusminus


Hy plusminus :)

Is there any solution to make "INNER JOIN"

thanks
saturne2008
Junior Developer
Junior Developer
 
Posts: 11
Joined: Wed Feb 24, 2010 9:40 pm

Top

Re: SQLite JOIN Problem.

Postby Moebius » Sat Apr 09, 2011 11:04 am

you should use rawQuery:

Code: Select all
myDB.rawQuery("select * from A inner join B .... ");
Moebius
Freshman
Freshman
 
Posts: 3
Joined: Wed Feb 02, 2011 11:14 am

Top

Return to General

Who is online

Users browsing this forum: No registered users and 4 guests