Hi Jose,

> I would like to get a bunch of records of IDs that I already know. For 
> example, this table called Jobs,
> rec,...,data,...
> 1,...,aaa,...
> 2,...,zzz,...
> ...
> ...
> 99,...,azz,...

In addition to the pure syntax answer of other, I suggest you also consider 
your broader application of this query. From where are you getting the IDs in 
the first place? If you are getting them from a preliminary query, you can 
probably combine both queries into one for much better SQL and performance. Do 
as much of the logic in SQL as you can to create internally consistent 
databases and less application code. It saves a lot of converting results back 
and forward from SQL to application objects.

For example, if your schema is:

create table Jobs
(       ID integer primary key not null
,       Title text collate nocase
,       Company text collate nocase
,       Date date
-- and more columns
)
;
create table "Job Skills"
(       ID integer primary key not null
,       Job integer not null references Jobs(ID)
,       Skill text not null collate nocase
,       unique (Job, Skill)
)
;

(You should instead normalize the "Job Skills" table by using a third "Skills" 
table, but that's another story.)

And your two queries are:

select Job from "Job Skills" where Skill = "SQLite";

which gives you a list of Job IDs, which you're then re-injecting into a second 
query:

select * from Jobs where ID in (87, 33, 27,2, 1)
order by ID desc
;

You can instead combine into one query:

select *
from Jobs join "Job Skills" on Jobs.ID = "Job Skills".Job
where Skill = "SQLite"
order by Jobs.ID desc
;

or, if your prefer:

select *
from Jobs
where ID in (select Job from "Job Skills" where Skill = "SQLite")
order by Jobs.ID desc
;

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to