pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Query question
8+ messages / 6 participants
[nested] [flat]
* SQL Query question
@ 2005-06-30 09:27 Nick Stone <nick@harelane.com>
0 siblings, 2 replies; 8+ messages in thread
From: Nick Stone @ 2005-06-30 09:27 UTC (permalink / raw)
To: pgsql-sql
Hi
Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have
a question on the following query:
SELECT
tbl1."TermTypeID",
tbl1."ParentID",
tbl1."KeywordID",
tbl1."Term",
tbl2."KeywordID"
FROM
"Terms" As tbl1 LEFT JOIN
"SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND
tbl2."StockID" = 1
WHERE
(tbl1."TermTypeID" >= 200) AND
(tbl1."TermTypeID" < 600) AND
(tbl1."IsSynonym" = false) AND
(tbl1."LanguageID" = 1)
ORDER BY
tbl1."TermTypeID",
tbl1."Term";
Why does the above query work fine and the folowing query not work? And as a
additional kind of spanner in the works I've tried the following on MS SQL
Server and Oracle both of which produce the correct results (i.e. the same
as the above query). NB: the Terms table always has data whereas the
SearchStore may or may not have any data.
SELECT
tbl1."TermTypeID",
tbl1."ParentID",
tbl1."KeywordID",
tbl1."Term",
tbl2."KeywordID"
FROM
"Terms" As tbl1 LEFT JOIN
"SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID"
WHERE
(tbl1."TermTypeID" >= 200) AND
(tbl1."TermTypeID" < 600) AND
(tbl1."IsSynonym" = false) AND
(tbl1."LanguageID" = 1) AND
(tbl2."StockID" = 1)
ORDER BY
tbl1."TermTypeID",
tbl1."Term";
Just to be obvious both queries as far as I can should return everything
from Terms and anything if it exists from SearchStore subject to the WHERE
clause parameters - obviously!
Many thanks in advance
Nick
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL Query question
@ 2005-06-30 11:18 Peter Eisentraut <peter_e@gmx.net>
parent: Nick Stone <nick@harelane.com>
1 sibling, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2005-06-30 11:18 UTC (permalink / raw)
To: Nick Stone <nick@harelane.com>; +Cc: pgsql-sql
Am Donnerstag, 30. Juni 2005 11:27 schrieb Nick Stone:
> SELECT
> tbl1."TermTypeID",
> tbl1."ParentID",
> tbl1."KeywordID",
> tbl1."Term",
> tbl2."KeywordID"
> FROM
> "Terms" As tbl1 LEFT JOIN
> "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID"
> WHERE
> (tbl1."TermTypeID" >= 200) AND
> (tbl1."TermTypeID" < 600) AND
> (tbl1."IsSynonym" = false) AND
> (tbl1."LanguageID" = 1) AND
> (tbl2."StockID" = 1)
> ORDER BY
> tbl1."TermTypeID",
> tbl1."Term";
>
> Just to be obvious both queries as far as I can should return everything
> from Terms and anything if it exists from SearchStore subject to the WHERE
> clause parameters - obviously!
The condition (tbl2."StockID" = 1) will remove all rows that have null values
in the tbl2 fields, thus making your left join useless. Perhaps you should
change that to (tbl2."StockID" = 1 OR tbl2."StockID" IS NULL) -- or use your
first version.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL Query question
@ 2005-06-30 11:22 Richard Huxton <dev@archonet.com>
parent: Nick Stone <nick@harelane.com>
1 sibling, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2005-06-30 11:22 UTC (permalink / raw)
To: Nick Stone <nick@harelane.com>; +Cc: pgsql-sql
Nick Stone wrote:
> Hi
>
> Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have
> a question on the following query:
> FROM
> "Terms" As tbl1 LEFT JOIN
> "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND
> tbl2."StockID" = 1
> Why does the above query work fine and the folowing query not work? And as a
> additional kind of spanner in the works I've tried the following on MS SQL
> Server and Oracle both of which produce the correct results
> FROM
> "Terms" As tbl1 LEFT JOIN
> "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID"
> WHERE
...
> (tbl2."StockID" = 1)
Hmm - I'm not sure that MSSQL/Oracle are giving the "right" answer here.
I assume the tbl2.stockid test is the issue here, and we apply the test
after the join whereas the others push the condition inside the join.
I'm inclined to prefer PG's way of doing things, since it means you get
what you explicitly asked for (to my point of view anyway). Not sure
what the SQL spec says though, and in the end I suppose that's the only
way to decide "right".
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL Query question
@ 2005-06-30 12:22 Nick Stone <nick@harelane.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Nick Stone @ 2005-06-30 12:22 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
Thanks for the reply at least that explains it.
Nick
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 30 June 2005 12:22
To: Nick Stone
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] SQL Query question
Nick Stone wrote:
> Hi
>
> Whilst I'm not new to SQL I am reasonably new to Postgres and as such
> I have a question on the following query:
> FROM
> "Terms" As tbl1 LEFT JOIN
> "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND
> tbl2."StockID" = 1 Why does the above query work fine and the folowing
> query not work? And as a additional kind of spanner in the works I've
> tried the following on MS SQL Server and Oracle both of which produce
> the correct results
> FROM
> "Terms" As tbl1 LEFT JOIN
> "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID"
> WHERE
...
> (tbl2."StockID" = 1)
Hmm - I'm not sure that MSSQL/Oracle are giving the "right" answer here.
I assume the tbl2.stockid test is the issue here, and we apply the test
after the join whereas the others push the condition inside the join.
I'm inclined to prefer PG's way of doing things, since it means you get what
you explicitly asked for (to my point of view anyway). Not sure what the SQL
spec says though, and in the end I suppose that's the only way to decide
"right".
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL query question
@ 2007-10-07 19:16 Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rodrigo De León @ 2007-10-07 19:16 UTC (permalink / raw)
To: pgsql-sql
On Sep 21, 12:09 am, stevenleong...@gmail.com wrote:
> Write the query (or queries if necessary) needed to count the number
> of employees in each employee's department who are paid more than
> their manager.
SELECT e.dept, COALESCE (SUM (1), 0) AS n
FROM employees e JOIN employees m
ON (e.empid = m.mgrid AND e.salary > m.salary)
GROUP BY e.dept
ORDER BY e.dept
^ permalink raw reply [nested|flat] 8+ messages in thread
* sql query question ?
@ 2007-12-29 10:52 Trilok Kumar <trilokumar@yahoo.co.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Trilok Kumar @ 2007-12-29 10:52 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I have a table called
vehicle_duty_cycle_summary
vehicle_master_id | starting_odometer |
ending_odometer | login_time |
logout_time
-------------------+-------------------+-----------------+----------------------------+----------------------------
4 | 53379.00 | 53504.00 | 2006-12-19
16:19:16.584547 | 2006-12-20 07:12:57.716907
4 | 51310.00 |
51457.00 | 2006-12-05 16:04:51.585441 | 2006-12-06
07:18:10.251535
4 | 42411.00 |
42411.00 | 2006-09-14 16:03:49.541442 | 2006-09-15
08:24:41.308339
4 | 54012.00 |
54146.00 | 2006-12-25 16:14:31.313728 | 2006-12-26
07:05:55.82487
4 | 43071.00 |
43181.00 | 2006-09-21 11:24:43.466766 | 2006-09-22
07:10:09.362792
4 | 51208.00 |
51305.00 | 2006-12-04 16:14:14.600105 | 2006-12-05
08:03:38.139832
4 | 50235.00 |
50370.00 | 2006-11-27 15:55:48.866925 | 2006-11-28
07:27:25.898991
4 | 53231.00 |
53362.00 | 2006-12-18 16:06:42.764133 | 2006-12-19
07:06:03.450072
4 | 52656.00 |
52818.00 | 2006-12-13 16:03:47.189303 | 2006-12-14
07:59:11.463733
4 | 50801.00 |
50880.00 | 2006-12-02 16:03:55.666321 | 2006-12-03
06:53:21.433746
9 | 85360.00 |
85493.00 | 2007-06-10 07:17:12.330974 | 2007-06-10
22:11:04.422656
9 | 78009.00 |
78042.00 | 2007-03-12 17:53:18.794001 | 2007-03-12
20:42:39.439647
9 | 84529.00 |
84679.00 | 2007-06-01 06:42:09.306306 | 2007-06-01
20:35:54.317172
9 | 78058.00 |
78149.00 | 2007-03-13 10:08:48.696709 | 2007-03-13
21:50:31.136412
9 | 86506.00 |
86595.00 | 2007-06-21 09:28:40.504082 | 2007-06-21
23:15:41.862292
9 | 78155.00 |
78239.00 | 2007-03-14 09:32:58.512817 | 2007-03-14
20:58:24.36362
9 | 84894.00 |
85012.00 | 2007-06-04 07:59:00.896969 | 2007-06-04
18:42:13.791974
9 | 78435.00 |
78494.00 | 2007-03-16 07:48:23.626402 | 2007-03-16
21:39:09.479043
9 | 83992.00 |
84045.00 | 2007-05-25 07:25:20.462928 | 2007-05-25
21:23:43.697577
9 | 78506.00 |
78595.00 | 2007-03-17 08:01:06.003564 | 2007-03-17
19:48:32.383689
9 | 85493.00 |
85640.00 | 2007-06-11 06:58:03.052538 | 2007-06-11
22:56:13.134053
9 | 78279.00 |
78395.00 | 2007-03-15 08:00:58.198265 | 2007-03-15
21:39:00.052173
I would like to compute the following on this table.
Idle time of vehicel=(ending_odometer reading of the
previous day -
starting_odometer reading of the present day) for
every vehicle
can anybody help me this issue.
Thanks in advance.
Trilok
__________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql query question ?
@ 2007-12-29 17:22 Shane Ambler <pgsql@Sheeky.Biz>
parent: Trilok Kumar <trilokumar@yahoo.co.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Shane Ambler @ 2007-12-29 17:22 UTC (permalink / raw)
To: Trilok Kumar <trilokumar@yahoo.co.uk>; +Cc: pgsql-sql
Trilok Kumar wrote:
> Hi All,
>
> I have a table called
>
> vehicle_duty_cycle_summary
>
>
> vehicle_master_id | starting_odometer |
> ending_odometer | login_time |
> logout_time
> -------------------+-------------------+-----------------+----------------------------+----------------------------
>
> 4 | 53379.00 | 53504.00 | 2006-12-19
> 16:19:16.584547 | 2006-12-20 07:12:57.716907
>
> I would like to compute the following on this table.
>
> Idle time of vehicel=(ending_odometer reading of the
> previous day -
> starting_odometer reading of the present day) for
> every vehicle
I would think your naming may be confusing and may not be
implemented(recorded?) very well.
I think Idle Time is a misleading name by your explanation - Idle time
would be defined as (logout_time - previous login_time) which gives you
the time the vehicle was sitting in the garage.
What you want may be better called unmetered_travel and would be the
distance traveled between login_time and logout_time
This would simply be
select vehicle_master_id,
(ending_odometer - starting_odometer) as unmetered_travel
from vehicle_duty_cycle_summary;
Going by the naming you have used it would appear that you are recording
the time spent in the garage (going by the data you have shown I would
say this is a company car garage not a repair shop)
One record would appear to record the time the car is in the garage -
login_time would be the time the employee returned the car and
logout_time would be when the car next went out to someone.
I would think you want the opposite of that - the time and odometer
reading when an employee takes the car and the time and odometer of when
it is returned and the employee_id of who had it. This will give you who
used the car at what time and what distances they travelled (which of
course would be work related travel)
Going with those changes -
The distance traveled by an employee is easy to workout, if you wanted
to workout the unmetered (non-work) distance traveled you could try
something like (untested) -
select
v1.vehicle_master_id
, v1.starting_odometer -
(select v2.ending_odometer
from vehicle_duty_cycle_summary v2
where v2.vehicle_master_id = v1.vehicle_master_id
and v2.login_time < v1.logout_time
order by v2.login_time desc limit 1)
as unmetered_travel
from vehicle_duty_cycle_summary v1
where v1.vehicle_master_id = 4;
I would calculate idle time as -
select
v1.vehicle_master_id
, v1.logout_time -
(select v2.login_time
from vehicle_duty_cycle_summary v2
where v2.vehicle_master_id = v1.vehicle_master_id
and v2.login_time < v1.logout_time
order by v2.login_time desc limit 1)
as unmetered_travel
from vehicle_duty_cycle_summary v1
where v1.vehicle_master_id = 4;
If this isn't the way it should work you should be able to adapt the
query to match your definition of idle time.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql query question ?
@ 2007-12-31 13:53 Trilok Kumar <trilokumar@yahoo.co.uk>
parent: Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 0 replies; 8+ messages in thread
From: Trilok Kumar @ 2007-12-31 13:53 UTC (permalink / raw)
To: Shane Ambler <pgsql@Sheeky.Biz>; +Cc: pgsql-sql
Dear Shane,
Thanks for the reply and your observation about the
word i have used. It is idle odometer reading.
The actual Scenario is that the vehicle is taken by
the driver.
When he comes the next day. He is suppose to login
again.
Here i am trying to find out how much distance has the
vehicle travelled before it login again,give a date
range and the vehicle id.
The below query would give me the total odometer
reading during a single trip.
select vehicle_master_id,
(ending_odometer - starting_odometer) as
unmetered_travel
from vehicle_duty_cycle_summary;
Thanks
Trilok
--- Shane Ambler <pgsql@Sheeky.Biz> wrote:
> Trilok Kumar wrote:
> > Hi All,
> >
> > I have a table called
> >
> > vehicle_duty_cycle_summary
> >
> >
> > vehicle_master_id | starting_odometer |
> > ending_odometer | login_time |
>
> > logout_time
> >
>
-------------------+-------------------+-----------------+----------------------------+----------------------------
> >
> > 4 | 53379.00 | 53504.00 |
> 2006-12-19
> > 16:19:16.584547 | 2006-12-20 07:12:57.716907
>
> >
> > I would like to compute the following on this
> table.
> >
> > Idle time of vehicel=(ending_odometer reading of
> the
> > previous day -
> > starting_odometer reading of the present day) for
> > every vehicle
>
> I would think your naming may be confusing and may
> not be
> implemented(recorded?) very well.
>
> I think Idle Time is a misleading name by your
> explanation - Idle time
> would be defined as (logout_time - previous
> login_time) which gives you
> the time the vehicle was sitting in the garage.
>
> What you want may be better called unmetered_travel
> and would be the
> distance traveled between login_time and logout_time
> This would simply be
> select vehicle_master_id,
> (ending_odometer - starting_odometer) as
> unmetered_travel
> from vehicle_duty_cycle_summary;
>
> Going by the naming you have used it would appear
> that you are recording
> the time spent in the garage (going by the data you
> have shown I would
> say this is a company car garage not a repair shop)
>
> One record would appear to record the time the car
> is in the garage -
> login_time would be the time the employee returned
> the car and
> logout_time would be when the car next went out to
> someone.
> I would think you want the opposite of that - the
> time and odometer
> reading when an employee takes the car and the time
> and odometer of when
> it is returned and the employee_id of who had it.
> This will give you who
> used the car at what time and what distances they
> travelled (which of
> course would be work related travel)
>
> Going with those changes -
>
> The distance traveled by an employee is easy to
> workout, if you wanted
> to workout the unmetered (non-work) distance
> traveled you could try
> something like (untested) -
>
> select
> v1.vehicle_master_id
> , v1.starting_odometer -
> (select v2.ending_odometer
> from vehicle_duty_cycle_summary v2
>
> where v2.vehicle_master_id = v1.vehicle_master_id
> and v2.login_time < v1.logout_time
>
> order by v2.login_time desc limit 1)
> as unmetered_travel
>
> from vehicle_duty_cycle_summary v1
>
> where v1.vehicle_master_id = 4;
>
>
> I would calculate idle time as -
>
> select
> v1.vehicle_master_id
> , v1.logout_time -
> (select v2.login_time
> from vehicle_duty_cycle_summary v2
>
> where v2.vehicle_master_id = v1.vehicle_master_id
> and v2.login_time < v1.logout_time
>
> order by v2.login_time desc limit 1)
> as unmetered_travel
>
> from vehicle_duty_cycle_summary v1
>
> where v1.vehicle_master_id = 4;
>
>
> If this isn't the way it should work you should be
> able to adapt the
> query to match your definition of idle time.
>
>
> --
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz
>
___________________________________________________________
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-12-31 13:53 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-30 09:27 SQL Query question Nick Stone <nick@harelane.com>
2005-06-30 11:18 ` Peter Eisentraut <peter_e@gmx.net>
2005-06-30 11:22 ` Richard Huxton <dev@archonet.com>
2005-06-30 12:22 ` Nick Stone <nick@harelane.com>
2007-10-07 19:16 Re: SQL query question Rodrigo De León <rdeleonp@gmail.com>
2007-12-29 10:52 sql query question ? Trilok Kumar <trilokumar@yahoo.co.uk>
2007-12-29 17:22 ` Re: sql query question ? Shane Ambler <pgsql@Sheeky.Biz>
2007-12-31 13:53 ` Re: sql query question ? Trilok Kumar <trilokumar@yahoo.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox