CODEWITHSUNDEEP
c#sqlite
atwellpub
atwellpub

Reputation: 6010

Executing a complex query in SQLite

I've a complex query with SQLite that seeks to grab a total from 3 related tables.

The following is an example of my tables:

----
cities
----
id, name

----
surveys
----
id, cities_id

----
population
----
id, survey_id

I need to find the total number of surveyed people for each city.

Here's the approach I've been taking using c#, but I believe there should be a straighter way to do this through SQLite. Plus, my brain is not solving my final step using c#.

                        //get a list of all cities
                        List<string> cities = new List<string>();

                        cmd.CommandText = "SELECT * from cities ";
                        cmd.ExecuteScalar();
                        SqlCeDataReader myReader = cmd.ExecuteReader();
                        while (myReader.Read())
                        {
                                    list_cities.Add(myReader["id"].ToString().Trim());
                        }


                        //get survey ids related to these citities
                        List<list_key_val> list_survey_ids = new List<list_key_val>();
                        foreach (String city_id in cities)
                        {
                            cmd.CommandText = "SELECT * from surveys WHERE city_id = '" + city_id + "'";
                            cmd.ExecuteScalar();
                            SqlCeDataReader myReader = cmd.ExecuteReader();
                            while (myReader.Read())
                            {
                                    list_survey_ids.Add(new list_key_val()
                                    {
                                        key = city_id,
                                        val = myReader["id"].ToString().Trim()
                                    });
                            }
                        }

                        //find total surveyed person count for each survey id.
                        List<list_key_val> list_surveyed_total = new List<list_key_val>();
                        foreach (String survey_id in list_survey_ids)
                        {
                            cmd.CommandText = "SELECT COUNT(*) from population WHERE survey_id = '" + survey_id + "'";
                            cmd.ExecuteScalar();

                            list_surveyed_total.Add(new list_key_val()
                            {
                                key = survey_id,
                                val =  count = (int)cmd.ExecuteScalar();
                            }                   
                        }

                        //find surveyed count as city id -> survey id -> surveyed count
                        List<list_key_val> list_city_surveyed_total = new List<list_key_val>();
                        foreach (String city_id in list_cities)
                        {
                            //dar... complex nested key calculations here, possibly?
                        }

I understand that SQLite has some limitation when it comes to combining queries. Thank you for any and all help!

Upvotes: 0

Views: 939

Answers (3)

k.m
k.m

Reputation: 31454

You should be able to pull all that information in one go, just alter your query:

var query = 
    "SELECT c.name, s.id, COUNT(*) " +
    "FROM cities c " +
    "JOIN surveys s ON (c.id = s.cities_id) " +
    "JOIN population p ON (s.id = p.survey_id) " +
    "GROUP BY c.name, s.id";


cmd.CommandText = query;
SqlCeDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
    Console.WriteLine("City {0}, survey {1} = {2}", myReader[0], myReader[1], 
        myReader[2]);
}

Upvotes: 1

Jer In Chicago
Jer In Chicago

Reputation: 828

Hard to tell if I got it right but you should be able to perform the operation in a single query... something like:

select cities.name, surveys.id, count(*) total
from cities, surveys, population
where cities.id = surveys.cities_id and surveys.id=population.survey_id
group by cities.name, surveys.id

Should give you something like:

Chicago, 1, 4000 Chicago, 2, 3355 Denver, 1, 2580 etc....

Ok, here is a dump of a test I just did in sqlite

sqlite> create table cities (id integer primary key, name text);
sqlite> create table surveys (id integer primary key, cities_id integer);
sqlite> create table population (id integer primary key, survey_id integer);
sqlite> insert into cities values (1, 'Chicago');
sqlite> insert into cities values (2, 'Denver');
sqlite> insert into surveys values (1, 1);
sqlite> insert into surveys values (2, 1);
sqlite> insert into surveys values (3, 2);
sqlite> insert into population values (1, 1);
sqlite> insert into population values (2, 1);
sqlite> insert into population values (3, 1);
sqlite> insert into population values (4, 1);
sqlite> insert into population values (5, 2);
sqlite> insert into population values (6, 2);
sqlite> insert into population values (7, 2);
sqlite> insert into population values (8, 3);
sqlite> insert into population values (9, 3);
sqlite> select cities.name, surveys.id, count(*) total
   ...> from cities, surveys, population
   ...> where cities.id = surveys.cities_id and
   ...> surveys.id=population.survey_id
   ...> group by cities.name, surveys.id
   ...> ;
Chicago|1|4
Chicago|2|3
Denver|3|2
sqlite>

Upvotes: 1

elrado
elrado

Reputation: 5272

per city AND survey:

select count(c.id) as num, c.name, s.id as survey from cities c, sirveys s, population p
where
c.id = s.cities_id AND
s.id = p.survey_id
group by c.name, s.id

per city:

select count(c.id) as num, c.name, from cities c, sirveys s, population p
where
c.id = s.cities_id AND
s.id = p.survey_id
group by c.name

Upvotes: 1

Related Questions