\On 11/18/06, Micro Mega Dust <[EMAIL PROTECTED]> wrote:
Hi,
I'm a novice using databases, and I have a doubt about the working of
SQL language: I have a system of user group, where each group is a
table in sqlite_master, (each table of a group contains the UIDs of
the members). But I don't know how to get the groups of a user, in a
single query.
I tried this:
SELECT name FROM sqlite_master WHERE 'foo' IN name /* foo is a UID */;
But don't work, "name" is used like a literal value.
Is possible? Thanks!
create table user (
id integer PRIMARY KEY AUTOINCREMENT,
name text
);
create table grp(
id integer PRIMARY KEY AUTOINCREMENT,
name text
);
create table user_group(
user_id integer,
group_id integer
);
insert into grp(name) values( 'super user' );
-- id will be assigned 1 automatically.
insert into grp(name) values( 'regular user' );
-- id will be assigned 2 automatically.
insert into user(name) values( 'Micro Mega Dust' );
-- id will be assigned 1 automatically.
insert into user_group(user_id,group_id) values( 1, 1 );
insert into user_group(user_id,group_id) values( 1, 2 );
select grp.name, user.name
from grp
inner join user_group on grp.id = user_group.group_id
inner join user on user_group.user_id
;
C:\temp>sqlite3 test.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table user (
...> id integer PRIMARY KEY AUTOINCREMENT,
...> name text
...> );
sqlite>
sqlite> create table grp(
...> id integer PRIMARY KEY AUTOINCREMENT,
...> name text
...> );
sqlite>
sqlite> create table user_group(
...> user_id integer,
...> group_id integer
...> );
sqlite>
sqlite>
sqlite> insert into grp(name) values( 'super user' );
sqlite> -- id will be assigned 1 automatically.
sqlite> insert into grp(name) values( 'regular user' );
sqlite> -- id will be assigned 2 automatically.
sqlite>
sqlite> insert into user(name) values( 'Micro Mega Dust' );
sqlite> -- id will be assigned 1 automatically.
sqlite>
sqlite> insert into user_group(user_id,group_id) values( 1, 1 );
sqlite> insert into user_group(user_id,group_id) values( 1, 2 );
sqlite>
sqlite>
sqlite> select grp.name, user.name
...> from grp
...> inner join user_group on grp.id = user_group.group_id
...> inner join user on user_group.user_id
...> ;
super user|Micro Mega Dust
regular user|Micro Mega Dust
sqlite>
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite
Cthulhu Bucks!
http://www.cthulhubucks.com
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
