Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Keith Medcalf wrote
> Well, "paint" is to draw your output.

Thank you.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

Well, "paint" is to draw your output.  So, for example, you can output an 
arbitrary matrix like this:

select columnHeader from theColumns order by columnHeader;

from which you can output ""
then for each row retrieved output "" columnHeader ""
and when you run out of rows
""
and keep track of how many rows you read.

Then select your data and paint it

select data from 

and if you are currently on column 0 output ""
then output "" data ""
and if you just output the last column reset your internal column counter to 0 
and output ""
and when you run out of stuff to output, then write ""

voila -- you just "painted an html table"


-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Winfried
>Sent: Thursday, 24 October, 2019 17:11
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Simon Slavin-3 wrote
>> https://sqlite.org/books.html
>
>Thanks.
>
>"You have to paint the output table yourself." "You still have to "paint
>the
>fence" yourself, though now the table data is not sparse, thus easier to
>paint"
>
>Looks like "paint" is DB lingo: Does it mean formatting the output after
>running the right SELECT ?
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Simon Slavin-3 wrote
> https://sqlite.org/books.html

Thanks.

"You have to paint the output table yourself." "You still have to "paint the
fence" yourself, though now the table data is not sparse, thus easier to
paint"

Looks like "paint" is DB lingo: Does it mean formatting the output after
running the right SELECT ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Simon Slavin]

On 24 Oct 2019, at 10:18am, Winfried  wrote:

> Is there a good book you would recommend to learn about SQLite, especially 
> regarding SELECT?

A list already made:



Books long and short, concentrating on different things.

You might also be interested in books about SQL, the query language used by 
SQLite and many other database engines.  This would include various complicated 
things that can be done using SELECT.  Unfortunately I don't learn well from 
books, so I can't recommend any.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Thank you very much to all. There's no way I could have come up with those
queries.

Considering the wealth/complexity of SELECT even in SQLite, it seems a whole
book could be written on just that command.

https://www.sqlite.org/images/syntax/select-stmt.gif

Is there a good book you would recommend to learn about SQLite, especially
regarding SELECT?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

On Tuesday, 22 October, 2019 23:24, Doug  wrote:

>Please explain one phrase in the select:
>  total(1) over (partition by city_txt, mode_txt)
>Is "total" a function and does "1" refer to city_txt?
>I can't wrap my head around what's going on with this phrase.

total() is a built-in aggregate like sum() -- there are just some minor (but 
important) differences.

sum() returns NULL if there was nothing to sum.  Also, if the values were all 
integers and the result fits in an integer, an integer is returned.

total() returns 0.0 if is has nothing to total, and the returned value is 
always floating-point.

So total(1) means the floating-point value of adding up all the 1 values ... 
that it, it is count(*) in floating-point, or basically cast(count(*) as float) 
but somewhat shorter.

This is because the integer expression 37/154 has the integer result 0, however 
the floating point expression 37.0 / 154.0 has result 0.24025974025974

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Doug]

Please explain one phrase in the select:
  total(1) over (partition by city_txt, mode_txt)
Is "total" a function and does "1" refer to city_txt?
I can't wrap my head around what's going on with this phrase.
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Keith Medcalf
> Sent: Tuesday, October 22, 2019 7:11 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Can SQLite do this in a single query?
> 
> CREATE TABLE employees(employee_id, city_id, mode_id);
> CREATE TABLE citys(city_id, city_txt);
> CREATE TABLE modes(mode_id, mode_txt);
> 
>   select city_txt,
>  mode_txt,
>  total(1) over (partition by city_txt, mode_txt) /
> total(1) over (partition by city_txt) as percentage
> from employees, citys, modes
>where employees.city_id == citys.city_id
>  and employees.mode_id == modes.mode_id
> group by city_txt, mode_txt
> group by city_txt, mode_txt;
> 
> You have to paint the output table yourself.
> 
> --
> The fact that there's a Highway to Hell but only a Stairway to
> Heaven says a lot about anticipated traffic volume.
> 
> >-Original Message-
> >From: sqlite-users 
> On
> >Behalf Of Winfried
> >Sent: Tuesday, 22 October, 2019 07:23
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: Re: [sqlite] Can SQLite do this in a single query?
> >
> >Yes, I forgot the column mode_id in the Employee's table.
> >
> >Thanks, I'll read up on the features SQLite's SELECT has to
> offer. At
> >worst,
> >I'll just run a simpler query multiple times.
> >
> >https://www.sqlite.org/lang_select.html
> >
> >
> >
> >--
> >Sent from: http://sqlite.1065341.n5.nabble.com/
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

This eliminates the duplicate denominator calculation:

  select city_txt,
 mode_txt,
 (
  select total(1)
from employees, citys, modes
   where employees.city_id == citys.city_id
 and employees.mode_id == modes.mode_id
 and citys.city_id == C.city_id
 and modes.mode_id == M.mode_id
 ) / employee_count as percentage
from (
select city_txt,
   citys.city_id,
   total(1) as employee_count
  from employees, citys
 where employees.city_id == citys.city_id
  group by city_txt
  order by city_txt
 ) as C, modes as M
order by city_txt, mode_txt;

and of course you can wrap it in a classifier if you like:

  select city_txt,
 sum(case when mode_txt = 'WALKING' then percentage end) as Walking,
 sum(case when mode_txt = 'CYCLING' then percentage end) as Cycling,
 sum(case when mode_txt = 'PUBLIC TRANSIT' then percentage end) as 
"Public Transit",
 sum(case when mode_txt = 'CAR' then percentage end) as Car,
 sum(case when mode_txt not in ('WALKING', 'CYCLING', 'PUBLIC TRANSIT', 
'CAR') then percentage end) as Other
from (
  select city_txt,
 mode_txt,
 (
  select total(1)
from employees, citys, modes
   where employees.city_id == citys.city_id
 and employees.mode_id == modes.mode_id
 and citys.city_id == C.city_id
 and modes.mode_id == M.mode_id
 ) / employee_count as percentage
from (
select city_txt,
   citys.city_id,
   total(1) as employee_count
  from employees, citys
 where employees.city_id == citys.city_id
  group by city_txt
  order by city_txt
 ) as C, modes as M
 )
group by city_txt
order by city_txt;

This is "standard SQL" and should work with just about any RDBMS using SQL of 
any variation.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 22 October, 2019 09:44
>To: SQLite mailing list 
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>
>Hmmm.  That gives the wrong answers compared to doing it the "old
>fashioned way".  You still have to "paint the fence" yourself, though now
>the table data is not sparse, thus easier to paint (since each value goes
>in the next cell, and you start a new row when the city_txt changes).
>You calculate the denominator excessively, but if really necessary you
>can optimize that.
>
>  select city_txt,
> mode_txt,
> (select total(1)
>from employees, citys, modes
>   where employees.city_id == citys.city_id
> and employees.mode_id == modes.mode_id
> and citys.city_id == C.city_id
> and modes.mode_id == M.mode_id) / (select total(1)
>  from employees, citys
> where employees.city_id
>== citys.city_id
>   and citys.city_id ==
>C.City_id) as percentage
>from citys C, modes M
>order by city_txt, mode_txt;
>
>
>and you get the column headings thusly (the left most column is of course
>your static text "City") ...
>
>  select mode_txt
>from modes
>order by mode_txt
>
>or if you do not like to put static column headings in your fence
>painter, then:
>
>select 'City'
>union all
>select mode_txt
>  from (select mode_txt
>  from modes
>  order by  mode_txt);
>
>Of course, perhaps you are using arbitrary "data hiding" headings and not
>the actual data (a method often used by PHB's to prove an pre-ordained
>outcome) in which case other methods may be more appropriate to "hide"
>what you do not want to see and provide it in a format compatible with
>that pre-ordained output).
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users  On
>>Behalf Of Keith Medcalf
>>Sent: Tuesday, 22 October, 2019 08:11
>>To: SQLite mailing list 
>>Subject: Re: [sqlite] Can SQLite do this in a single query?
>>
>>CREATE TABLE employees(employee_id, city_id, mode_id);
>>CREATE TABLE citys(city_id, city_txt);
>>C

Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

Hmmm.  That gives the wrong answers compared to doing it the "old fashioned 
way".  You still have to "paint the fence" yourself, though now the table data 
is not sparse, thus easier to paint (since each value goes in the next cell, 
and you start a new row when the city_txt changes).  You calculate the 
denominator excessively, but if really necessary you can optimize that.

  select city_txt,
 mode_txt,
 (select total(1)
from employees, citys, modes
   where employees.city_id == citys.city_id
 and employees.mode_id == modes.mode_id
 and citys.city_id == C.city_id
 and modes.mode_id == M.mode_id) / (select total(1)
  from employees, citys
 where employees.city_id == 
citys.city_id
   and citys.city_id == 
C.City_id) as percentage
from citys C, modes M
order by city_txt, mode_txt;


and you get the column headings thusly (the left most column is of course your 
static text "City") ...

  select mode_txt 
from modes 
order by mode_txt

or if you do not like to put static column headings in your fence painter, then:

select 'City'
union all
select mode_txt
  from (select mode_txt
  from modes
  order by  mode_txt);

Of course, perhaps you are using arbitrary "data hiding" headings and not the 
actual data (a method often used by PHB's to prove an pre-ordained outcome) in 
which case other methods may be more appropriate to "hide" what you do not want 
to see and provide it in a format compatible with that pre-ordained output).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 22 October, 2019 08:11
>To: SQLite mailing list 
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>CREATE TABLE employees(employee_id, city_id, mode_id);
>CREATE TABLE citys(city_id, city_txt);
>CREATE TABLE modes(mode_id, mode_txt);
>
>  select city_txt,
> mode_txt,
> total(1) over (partition by city_txt, mode_txt) / total(1) over
>(partition by city_txt) as percentage
>from employees, citys, modes
>   where employees.city_id == citys.city_id
> and employees.mode_id == modes.mode_id
>group by city_txt, mode_txt
>group by city_txt, mode_txt;
>
>You have to paint the output table yourself.
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users  On
>>Behalf Of Winfried
>>Sent: Tuesday, 22 October, 2019 07:23
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: Re: [sqlite] Can SQLite do this in a single query?
>>
>>Yes, I forgot the column mode_id in the Employee's table.
>>
>>Thanks, I'll read up on the features SQLite's SELECT has to offer. At
>>worst,
>>I'll just run a simpler query multiple times.
>>
>>https://www.sqlite.org/lang_select.html
>>
>>
>>
>>--
>>Sent from: http://sqlite.1065341.n5.nabble.com/
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[David Raymond]

Or something like this using the new filter clause from 3.30. As written it 
should show it with two decimal points.
(not tested for typos)

select
CITY_TXT as CITY,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'WALKING') / 
total(Employee_Count), 2) as WALKING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CYCLING') / 
total(Employee_Count), 2) as CYCLING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'PUBLIC TRANSIT') 
/ total(Employee_Count), 2) as PUBLIC_TRANSIT,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CAR') / 
total(Employee_Count), 2) as CAR,
round(100.0 * total(Employee_Count) filter (where MODE_TXT not in ('WALKING', 
'CYCLING', 'PUBLIC TRANSIT', 'CAR')) / total(Employee_Count), 2) as OTHER,
from
(
select
CITY_TXT,
MODE_TXT,
count(*) as Employee_Count
from
Employees
inner join Cities
using (CITY_ID)
inner join Mode
using (MODE_ID)
group by CITY_TXT, MODE_TXT
)
group by CITY_TXT
order by CITY;


-Original Message-
From: sqlite-users  On Behalf Of 
Keith Medcalf
Sent: Tuesday, October 22, 2019 10:11 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Can SQLite do this in a single query?

CREATE TABLE employees(employee_id, city_id, mode_id);
CREATE TABLE citys(city_id, city_txt);
CREATE TABLE modes(mode_id, mode_txt);

  select city_txt, 
 mode_txt, 
 total(1) over (partition by city_txt, mode_txt) / total(1) over 
(partition by city_txt) as percentage
from employees, citys, modes 
   where employees.city_id == citys.city_id 
 and employees.mode_id == modes.mode_id 
group by city_txt, mode_txt
group by city_txt, mode_txt;

You have to paint the output table yourself.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Winfried
>Sent: Tuesday, 22 October, 2019 07:23
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Yes, I forgot the column mode_id in the Employee's table.
>
>Thanks, I'll read up on the features SQLite's SELECT has to offer. At
>worst,
>I'll just run a simpler query multiple times.
>
>https://www.sqlite.org/lang_select.html
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

CREATE TABLE employees(employee_id, city_id, mode_id);
CREATE TABLE citys(city_id, city_txt);
CREATE TABLE modes(mode_id, mode_txt);

  select city_txt, 
 mode_txt, 
 total(1) over (partition by city_txt, mode_txt) / total(1) over 
(partition by city_txt) as percentage
from employees, citys, modes 
   where employees.city_id == citys.city_id 
 and employees.mode_id == modes.mode_id 
group by city_txt, mode_txt
group by city_txt, mode_txt;

You have to paint the output table yourself.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Winfried
>Sent: Tuesday, 22 October, 2019 07:23
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Yes, I forgot the column mode_id in the Employee's table.
>
>Thanks, I'll read up on the features SQLite's SELECT has to offer. At
>worst,
>I'll just run a simpler query multiple times.
>
>https://www.sqlite.org/lang_select.html
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Petr Jakeš]

> Yes, I forgot the column mode_id in the Employee's table.
>
> Thanks, I'll read up on the features SQLite's SELECT has to offer. At
> worst,
> I'll just run a simpler query multiple times.
>

Will the results of simple query multiple times help you somehow?

Think about how to join two or more tables together (not difficult to find
info about SQL JOIN)

My reply is to help you to try you think how SQL basically works.

>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Yes, I forgot the column mode_id in the Employee's table.

Thanks, I'll read up on the features SQLite's SELECT has to offer. At worst,
I'll just run a simpler query multiple times.

https://www.sqlite.org/lang_select.html



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Bart Smissaert]

> Not a clue.  I didn't write the schema.

I know, I asked the OP.

RBS

On Mon, Oct 21, 2019 at 4:16 PM Keith Medcalf  wrote:

>
> On Monday, 21 October, 2019 08:47, Bart Smissaert <
> bart.smissa...@gmail.com> wrote:
>
> >Shouldn't there be field MODE_ID in the Employee table?
>
> Not a clue.  I didn't write the schema.  Perhaps there is another table
> called MODATES that has a link to the employee (EMPLOYEE_ID) and a link to
> the mode (MODE_ID) together with the start and end dates to which that
> linkage applies.  And yet another table that called CIDATES that has a link
> the employee (EMPLOYEE_ID) and to the city (CITY_ID) together with the
> start and end dates to which that linkage applies.  (In which case there is
> still no solution since the problem is inadequately stated.
>
> Also, what about the people that walk except when it is raining and then
> they take the bus unless in either case they wake up late and drive
> themselves in their own car.  Except of course on Tuesday when the
> neighbour is going to the supermarket at the same time as they happen to be
> going to work so they hitch a ride with the neighbour in the neighbours car
> so they can smoke a phatty on the way, unless it is the second Tuesday of
> the month in which case they ride their bike because it is "Patch Tuesday"
> and, you know, just to be safe.  Except of course if there was an
> earthquake in which case they just stay home.
>
> The point being that there is no way to solve the problem stated using the
> given information, and no way to correct for the missing information since
> one has no clue what it is or how complicated it is.
>
> >On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf 
> wrote:
>
> >> On Monday, 21 October, 2019 08:31, Winfried 
> wrote:
> >>
> >> >Using the following tables, I need to find how employees from each
> >> city come to work.
> >>
> >> >== Employees table:
> >> >EMPLOYEE_ID | CITY_ID
> >> >Cities table:
> >> >CITY_ID | CITY_TXT
> >> >Mode table:
> >> >MODE_ID | MODE_TXT
> >> >
> >> >This is the type of output I need to get ultimately:
> >> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
> >> >City1 | 15% | 5% | 50% [ 25% | 5%
> >>
> >> No amount of queries or magical incantations will get you the results
> >you
> >> are asking for because there is no way to get from MODE_TXT to
> >CITY_TXT.
> >> That is, there is no way to compose the rest of the query as indicated
> >by
> >> the ... to obtain the data required to solve the problem:
> >>
> >> select EMPLOYEE_ID, CITY_TXT, MODE_TXT
> >>   from EMPLOYEES, CITIES, MODE
> >>  where 
> >>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

On Monday, 21 October, 2019 08:47, Bart Smissaert  
wrote:

>Shouldn't there be field MODE_ID in the Employee table?

Not a clue.  I didn't write the schema.  Perhaps there is another table called 
MODATES that has a link to the employee (EMPLOYEE_ID) and a link to the mode 
(MODE_ID) together with the start and end dates to which that linkage applies.  
And yet another table that called CIDATES that has a link the employee 
(EMPLOYEE_ID) and to the city (CITY_ID) together with the start and end dates 
to which that linkage applies.  (In which case there is still no solution since 
the problem is inadequately stated.

Also, what about the people that walk except when it is raining and then they 
take the bus unless in either case they wake up late and drive themselves in 
their own car.  Except of course on Tuesday when the neighbour is going to the 
supermarket at the same time as they happen to be going to work so they hitch a 
ride with the neighbour in the neighbours car so they can smoke a phatty on the 
way, unless it is the second Tuesday of the month in which case they ride their 
bike because it is "Patch Tuesday" and, you know, just to be safe.  Except of 
course if there was an earthquake in which case they just stay home.

The point being that there is no way to solve the problem stated using the 
given information, and no way to correct for the missing information since one 
has no clue what it is or how complicated it is.

>On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf  wrote:

>> On Monday, 21 October, 2019 08:31, Winfried  wrote:
>>
>> >Using the following tables, I need to find how employees from each
>> city come to work.
>>
>> >== Employees table:
>> >EMPLOYEE_ID | CITY_ID
>> >Cities table:
>> >CITY_ID | CITY_TXT
>> >Mode table:
>> >MODE_ID | MODE_TXT
>> >
>> >This is the type of output I need to get ultimately:
>> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
>> >City1 | 15% | 5% | 50% [ 25% | 5%
>>
>> No amount of queries or magical incantations will get you the results
>you
>> are asking for because there is no way to get from MODE_TXT to
>CITY_TXT.
>> That is, there is no way to compose the rest of the query as indicated
>by
>> the ... to obtain the data required to solve the problem:
>>
>> select EMPLOYEE_ID, CITY_TXT, MODE_TXT
>>   from EMPLOYEES, CITIES, MODE
>>  where 
>>
-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Petr Jakeš]

> Shouldn't there be field MODE_ID in the Employee table?
>

It looks like a high school homework to me :D
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Bart Smissaert]

Shouldn't there be field MODE_ID in the Employee table?

RBS

On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf  wrote:

>
> On Monday, 21 October, 2019 08:31, Winfried  wrote:
>
> >Using the following tables, I need to find how employees from each city
> >come to work.
>
> >== Employees table:
> >EMPLOYEE_ID | CITY_ID
> >Cities table:
> >CITY_ID | CITY_TXT
> >Mode table:
> >MODE_ID | MODE_TXT
> >
> >This is the type of output I need to get ultimately:
> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
> >City1 | 15% | 5% | 50% [ 25% | 5%
>
> No amount of queries or magical incantations will get you the results you
> are asking for because there is no way to get from MODE_TXT to CITY_TXT.
> That is, there is no way to compose the rest of the query as indicated by
> the ... to obtain the data required to solve the problem:
>
> select EMPLOYEE_ID, CITY_TXT, MODE_TXT
>   from EMPLOYEES, CITIES, MODE
>  where 
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

On Monday, 21 October, 2019 08:31, Winfried  wrote:

>Using the following tables, I need to find how employees from each city
>come to work.

>== Employees table:
>EMPLOYEE_ID | CITY_ID
>Cities table:
>CITY_ID | CITY_TXT
>Mode table:
>MODE_ID | MODE_TXT
>
>This is the type of output I need to get ultimately:
>CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
>City1 | 15% | 5% | 50% [ 25% | 5%

No amount of queries or magical incantations will get you the results you are 
asking for because there is no way to get from MODE_TXT to CITY_TXT.  That is, 
there is no way to compose the rest of the query as indicated by the ... to 
obtain the data required to solve the problem:

select EMPLOYEE_ID, CITY_TXT, MODE_TXT
  from EMPLOYEES, CITIES, MODE
 where  

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users