Re: [sqlite] Help with query

[Brian Pugh]

Fred,

A query I can understand!

This was my attempt, and worked to some degree, but yours is more workable
and, I feel, more accurate

"select
NewsData.Postcode,NewsData.Address1,Agents.Code,Agents.Shopname,Agents.Addre
ss1.Agents.Price from NewsData join Agents on NewsData.Postcode where
NewsData.Shop1>'' or NewsData.Shop2>'' order by NewsData.Postcode asc"

Thanks very much for your interest and help,

Brian

[sqlite] Help with query

[Brian Pugh]

Hi,

This query, "select distinct Shop1,Shop2,Postcode from NewsData where Shop1>'' or 
Shop2>'' order by Postcode asc"
works as I require, but I want to expand the query to include data from another table, 
and have got a bit stuck

The second table is called Agents. If the above query is successful, I want it to 
include the columns called
Code, Shopname, Address1 and Price, but only if the Shopname column in the Agents 
table matches either
Shop1 or Shop2 in the NewsData table

Can anyone help me with this?

Regards,

Brian Pugh

[sqlite] Changing a Field

[Brian Pugh]

Hi,

I need to do a multiple search and replace on the same field
in my database. Can anyone suggest a suitable bit of code
for this? I have over 100,000 records, so any automation is
very welcome!

Thanks for any suggestions,

Brian Pugh

[sqlite] Indexes

[Brian Pugh]

Hi,

My current database is mainly names and addresses. Most of my record viewing
is done by: "select * from TableName order by Zip,Address,No"

Zip, Address and No (house number) have separate indexes. On other databases
I have used, I have combined these three columns into one index, and used that
index to display records

I tried creating a new index, "create index DefView on TableName(Zip,Address,No)"

SQLite didn't complain about that, but when I tried to use the index, by using
"select * from TableName order by DefView" I didn't get any records returned
to view

I have obviously got the wrong end of the stick about indexes!

Could someone put me right, or point me gently in the right direction?

Thanks,

Brian Pugh

[sqlite] Re: Jump to record

[Brian Pugh]

Bronislav, Mrs Brisby and Derrell,

Thanks a lot for your interest. It now remains for me to try
your suggestions

Thanks again,

Brian

[sqlite] Jumping to a record

[Brian Pugh]

Hi,

I have a set of records selected, for example, with:

"select * from NewsData where (upper(Zip)>='HX1' and upper(Zip)<'HX2') order by 
Zip,Address,No"

There is an INTEGER PRIMARY KEY field in the database called ID

Given that I know I have a record with an ID of 12345, how would it be possible to 
"jump" to that
record, and still maintain my original select parameters and order?

I guess that this is down to a select within a select, but I'm not sure how to go 
about it. Well, I'll
qualify that - anything that I've tried hasn't worked yet!

Thanks a lot,

Brian Pugh,
Halifax, England

[sqlite] Select from a Select

[Brian Pugh]

Hello,

My current SQLite application is written with Pyxia's IBasic, and by nature of the 
database,
is form based. This works well now I have applied all the speed tweaks I have picked up
from this forum

I have added a button to the form to show records in Listview format. This works OK as 
long
as I send a second SELECT to the Listview for it to display the required range of 
records

Of course, I have already sent a SELECT to the main form-based window for it to show
my required range of records. How do I send the same SELECT to the listview for it to
show the already selected range?

I see there is "create view" and it probably appears to be what I want, but I have no
idea how to apply it, or make a "pre-packaged" select

Any answers or suggestions would be most gratefully welcomed

Thanks a lot,

Brian Pugh

[sqlite] Select from Select

[Brian Pugh]

Hi,

I would like to be able to run a query on my names and addresses
database, returning the following:

How many records in a certain Postcode area (Zip code to our
US friends!);
How many of those have telephones (ie, the field is blank if
they don't have a telephone);
How many with telephones can be canvassed (I have a field called
Canvass that has either a 'Y' or 'N' in there)

I can do the first (how many records), but I don't want to have
to run a query for every return needed. I think a "select from
select" is needed, but I don't know how to code it

Table is called NewsData; Postcode field is called Postcode;
Telephone field is called Tel; Canvass field is called Canvass

Many thanks for any help,

Brian Pugh