And for another question, how do I use records from one table to
select records from a second, such as:

CREATE TABLE answer SELECT * FROM table1 WHERE table1.field1 <>
table2.field1 AND table1.field2 <> table2.field2;

In other words, I need the records in the table "answer" to be those
in table 1 that do not match records in table2 based on comparing
column values.

On 10/28/2010 10:22 PM, Charles wrote:
> And for another question, how do I use records from one table to
> select records from a second, such as:
>
> CREATE TABLE answer SELECT * FROM table1 WHERE table1.field1<>
> table2.field1 AND table1.field2<>  table2.field2;
>
> In other words, I need the records in the table "answer" to be those
> in table 1 that do not match records in table2 based on comparing
> column values.

I'm not real clear on what you're asking for.  Can you post some sample 
data from both tables, and what you are looking for for output?


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Oct 28, 7:54=A0pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> On 10/28/2010 10:22 PM, Charles wrote:
>
> > And for another question, how do I use records from one table to
> > select records from a second, such as:
>
> > CREATE TABLE answer SELECT * FROM table1 WHERE table1.field1<>
> > table2.field1 AND table1.field2<> =A0table2.field2;
>
> > In other words, I need the records in the table "answer" to be those
> > in table 1 that do not match records in table2 based on comparing
> > column values.
>
> I'm not real clear on what you're asking for. =A0Can you post some sample
> data from both tables, and what you are looking for for output?

Sure.

TABLE 1
call_serial_number 	gps_cab_number 	gps_distance
100	219	5.00
101	219	6.00
102	219	7.00
103	219	8.00
100	213	8.00
101	213	7.00
102	213	6.00
103	213	5.00

TABLE 2
call_serial_number 	gps_cab_number 	date_rejected_by_cab
time_rejected_by_cab
103	219	2010-10-29	00:08:15
102	213	2010-10-29	00:08:17

ANSWER
call_serial_number 	gps_cab_number 	gps_distance
100	219	5.00
101	219	6.00
102	219	7.00
100	213	8.00
101	213	7.00
103	213	5.00

Answer consists of records in Table 1, minus those records in Table 2
based on a match for the call_serial_number and gps_cab_number.

On 2010-10-29 09:25, Charles wrote:
> On Oct 28, 7:54 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> On 10/28/2010 10:22 PM, Charles wrote:
>>
>> > And for another question, how do I use records from one table to
>> > select records from a second, such as:
>>
>> > CREATE TABLE answer SELECT * FROM table1 WHERE table1.field1<>
>> > table2.field1 AND table1.field2<>  table2.field2;
>>
>> > In other words, I need the records in the table "answer" to be those
>> > in table 1 that do not match records in table2 based on comparing
>> > column values.
>>
>> I'm not real clear on what you're asking for.  Can you post some sample
>> data from both tables, and what you are looking for for output?
> 
> Sure.
> 
> TABLE 1
> call_serial_number 	gps_cab_number 	gps_distance
> 100	219	5.00
> 101	219	6.00
> 102	219	7.00
> 103	219	8.00
> 100	213	8.00
> 101	213	7.00
> 102	213	6.00
> 103	213	5.00
> 
> TABLE 2
> call_serial_number 	gps_cab_number 	date_rejected_by_cab
> time_rejected_by_cab
> 103	219	2010-10-29	00:08:15
> 102	213	2010-10-29	00:08:17
> 
> ANSWER
> call_serial_number 	gps_cab_number 	gps_distance
> 100	219	5.00
> 101	219	6.00
> 102	219	7.00
> 100	213	8.00
> 101	213	7.00
> 103	213	5.00
> 
> Answer consists of records in Table 1, minus those records in Table 2
> based on a match for the call_serial_number and gps_cab_number.

MySQL does not support the except (sometimes named minus) operator, but
you can express the same thing via:

select call_serial_number, gps_cab_number, gps_distance
from table1 t1
where not exists (
	select 1 from table2 t2
	where (t1.call_serial_number, t1.gps_cab_number)
	    = (t2.call_serial_number, t2.gps_cab_number)
);

or by using an outer join:

select call_serial_number, gps_cab_number, gps_distance
from table1 t1
left join table t2
    on (t1.call_serial_number, t1.gps_cab_number)
     = (t2.call_serial_number, t2.gps_cab_number)
where t2.call_serial_number is null;

If either call_serial_number or gps_cab_number is nullable you may have
to add additional conditions to get the semantics right.

/Lennart

El 29/10/2010 9:25, Charles escribi�/wrote:
> On Oct 28, 7:54 pm, Jerry Stuckle<jstuck...@attglobal.net>  wrote:
>> On 10/28/2010 10:22 PM, Charles wrote:
>>
>>> And for another question, how do I use records from one table to
>>> select records from a second, such as:
>>
>>> CREATE TABLE answer SELECT * FROM table1 WHERE table1.field1<>
>>> table2.field1 AND table1.field2<>    table2.field2;
>>
>>> In other words, I need the records in the table "answer" to be those
>>> in table 1 that do not match records in table2 based on comparing
>>> column values.
>>
>> I'm not real clear on what you're asking for.  Can you post some sample
>> data from both tables, and what you are looking for for output?
>
> Sure.
>
> TABLE 1
> call_serial_number 	gps_cab_number 	gps_distance
> 100	219	5.00
> 101	219	6.00
> 102	219	7.00
> 103	219	8.00
> 100	213	8.00
> 101	213	7.00
> 102	213	6.00
> 103	213	5.00
>
> TABLE 2
> call_serial_number 	gps_cab_number 	date_rejected_by_cab
> time_rejected_by_cab
> 103	219	2010-10-29	00:08:15
> 102	213	2010-10-29	00:08:17
>
> ANSWER
> call_serial_number 	gps_cab_number 	gps_distance
> 100	219	5.00
> 101	219	6.00
> 102	219	7.00
> 100	213	8.00
> 101	213	7.00
> 103	213	5.00
>
> Answer consists of records in Table 1, minus those records in Table 2
> based on a match for the call_serial_number and gps_cab_number.

Hmmm...

SELECT t1.call_serial_number, t1.gps_cab_number, t1.gps_distance
FROM `TABLE 1` t1
LEFT JOIN `TABLE 2` t2 ON t1.call_serial_number=t2.call_serial_number
	AND t1.gps_cab_number=t2.gps_cab_number
WHERE t2.call_serial_number IS NULL



-- 
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Oct 29, 1:19=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-10-29 09:25, Charles wrote:
>
>
>
>
>
> > On Oct 28, 7:54 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> On 10/28/2010 10:22 PM, Charles wrote:
>
> >> > And for another question, how do I use records from one table to
> >> > select records from a second, such as:
>
> >> > CREATE TABLE answer SELECT * FROM table1 WHERE table1.field1<>
> >> > table2.field1 AND table1.field2<> =A0table2.field2;
>
> >> > In other words, I need the records in the table "answer" to be those
> >> > in table 1 that do not match records in table2 based on comparing
> >> > column values.
>
> >> I'm not real clear on what you're asking for. =A0Can you post some sam=
ple
> >> data from both tables, and what you are looking for for output?
>
> > Sure.
>
> > TABLE 1
> > call_serial_number =A0 =A0 =A0 =A0 gps_cab_number =A0gps_distance
> > 100 =A0 =A0 =A0 =A0219 =A0 =A0 5.00
> > 101 =A0 =A0 =A0 =A0219 =A0 =A0 6.00
> > 102 =A0 =A0 =A0 =A0219 =A0 =A0 7.00
> > 103 =A0 =A0 =A0 =A0219 =A0 =A0 8.00
> > 100 =A0 =A0 =A0 =A0213 =A0 =A0 8.00
> > 101 =A0 =A0 =A0 =A0213 =A0 =A0 7.00
> > 102 =A0 =A0 =A0 =A0213 =A0 =A0 6.00
> > 103 =A0 =A0 =A0 =A0213 =A0 =A0 5.00
>
> > TABLE 2
> > call_serial_number =A0 =A0 =A0 =A0 gps_cab_number =A0date_rejected_by_c=
ab
> > time_rejected_by_cab
> > 103 =A0 =A0 =A0 =A0219 =A0 =A0 2010-10-29 =A0 =A0 =A000:08:15
> > 102 =A0 =A0 =A0 =A0213 =A0 =A0 2010-10-29 =A0 =A0 =A000:08:17
>
> > ANSWER
> > call_serial_number =A0 =A0 =A0 =A0 gps_cab_number =A0gps_distance
> > 100 =A0 =A0 =A0 =A0219 =A0 =A0 5.00
> > 101 =A0 =A0 =A0 =A0219 =A0 =A0 6.00
> > 102 =A0 =A0 =A0 =A0219 =A0 =A0 7.00
> > 100 =A0 =A0 =A0 =A0213 =A0 =A0 8.00
> > 101 =A0 =A0 =A0 =A0213 =A0 =A0 7.00
> > 103 =A0 =A0 =A0 =A0213 =A0 =A0 5.00
>
> > Answer consists of records in Table 1, minus those records in Table 2
> > based on a match for the call_serial_number and gps_cab_number.
>
> MySQL does not support the except (sometimes named minus) operator, but
> you can express the same thing via:
>
> select call_serial_number, gps_cab_number, gps_distance
> from table1 t1
> where not exists (
> =A0 =A0 =A0 =A0 select 1 from table2 t2
> =A0 =A0 =A0 =A0 where (t1.call_serial_number, t1.gps_cab_number)
> =A0 =A0 =A0 =A0 =A0 =A0 =3D (t2.call_serial_number, t2.gps_cab_number)
> );
>
> or by using an outer join:
>
> select call_serial_number, gps_cab_number, gps_distance
> from table1 t1
> left join table t2
> =A0 =A0 on (t1.call_serial_number, t1.gps_cab_number)
> =A0 =A0 =A0=3D (t2.call_serial_number, t2.gps_cab_number)
> where t2.call_serial_number is null;
>
> If either call_serial_number or gps_cab_number is nullable you may have
> to add additional conditions to get the semantics right.

Not that I'm clear about this, but can you explain in lots of detail
on HOW the second one will process?  I'm still thinking in Paradox,
where "SUBTRACT TABLE2 FROM TABLE1" is supported.

And are you using "outer join" or "left join" in the statement?  You
preface it by explaining it as a "outer join" (digging for dictonary,
but that's not relevant), but the in the statement you state "left
join".

>
> If either call_serial_number or gps_cab_number is nullable you may have
> to add additional conditions to get the semantics right.
>

And while there is no null field in either table, one cold pray and
hope (and plan) for gps_match_cab_reject - table 2 in the example - to
be empty , I suppose.

On Oct 29, 5:00=A0am, Charles <ccha...@gmail.com> wrote:
> > If either call_serial_number or gps_cab_number is nullable you may have
> > to add additional conditions to get the semantics right.
>
> And while there is no null field in either table, one cold pray and
> hope (and plan) for gps_match_cab_reject - table 2 in the example - to
> be empty , I suppose.

And here's the error message:

SQL query:

CREATE TABLE answer SELECT *
FROM gps_match_distance
LEFT JOIN gps_match_cab_reject ON
( gps_match_distance.call_serial_number,
gps_match_distance.gps_cab_number ) =3D
( gps_match_cab_reject.call_serial_number,
gps_match_cab_reject.gps_cab_number )
WHERE gps_match_cab_reject.call_serial_number IS NULL ;



MySQL said:

#1060 - Duplicate column name 'call_serial_number'

On 2010-10-29 13:36, Charles wrote:
[...]
> And are you using "outer join" or "left join" in the statement?  You
> preface it by explaining it as a "outer join" (digging for dictonary,
> but that's not relevant), but the in the statement you state "left
> join".

left join is a shorter form of left outer join, Both variants are legal
according to standard.

select T1.x, T2.y from T1
left outer join T2
   on ...

is eqvivalent with (any complications introduced with nulls ignored):

select T1.x, T2.y from T1
inner join
select ... from T2
   on ...
union
select T1.x, cast(null as <type of T2.y>) as y... from T1
where not exists (
    select 1 from T2
    where ...
)

Hence:

select T1.x, T2.y from T1
left outer join T2
   on ...
where T2.y is null

is eqvivalent with

select T1.x, cast(null as <type of T2.y>) as y... from T1
where not exists (
    select 1 from T2
    where ...
)

Since you asked about the second solution I assume that the first one is
clear to you.

/Lennart

On Oct 29, 5:22=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-10-29 13:36, Charles wrote:
> [...]
>
> > And are you using "outer join" or "left join" in the statement? =A0You
> > preface it by explaining it as a "outer join" (digging for dictonary,
> > but that's not relevant), but the in the statement you state "left
> > join".
>
> left join is a shorter form of left outer join, Both variants are legal
> according to standard.
>
> select T1.x, T2.y from T1
> left outer join T2
> =A0 =A0on ...
>
> is eqvivalent with (any complications introduced with nulls ignored):
>
> select T1.x, T2.y from T1
> inner join
> select ... from T2
> =A0 =A0on ...
> union
> select T1.x, cast(null as <type of T2.y>) as y... from T1
> where not exists (
> =A0 =A0 select 1 from T2
> =A0 =A0 where ...
> )
>
> Hence:
>
> select T1.x, T2.y from T1
> left outer join T2
> =A0 =A0on ...
> where T2.y is null
>
> is eqvivalent with
>
> select T1.x, cast(null as <type of T2.y>) as y... from T1
> where not exists (
> =A0 =A0 select 1 from T2
> =A0 =A0 where ...
> )
>
> Since you asked about the second solution I assume that the first one is
> clear to you.

The second one is clearer and better defined.  I need to be able to do
this all over the place, so I might as well study what documents and
works better.

You'll find a posted error message, though.  Do I need to make the
column names unique across all tables?

On 2010-10-29 14:36, Charles wrote:
[...]
> 
> You'll find a posted error message, though.

If you post create table statements I can have a look. While you're at
post insert statements for a couple of rows that illustrates the problem
you are facing.

>  Do I need to make the
> column names unique across all tables?

No, on the contrary that's generally a bad idea. Let's say that we have
a table books. A possible key is isbn, and it doesn't make sense to name
it something else in related tables. However when an attribute play
several roles in a table, we are forced to rename at least one of them.
Not the situation here though


/Lennart

On Oct 29, 5:04=A0am, Charles <ccha...@gmail.com> wrote:
> On Oct 29, 5:00=A0am, Charles <ccha...@gmail.com> wrote:
>
> > > If either call_serial_number or gps_cab_number is nullable you may ha=
ve
> > > to add additional conditions to get the semantics right.
>
> > And while there is no null field in either table, one cold pray and
> > hope (and plan) for gps_match_cab_reject - table 2 in the example - to
> > be empty , I suppose.
>
> And here's the error message:
>
> SQL query:
>
> CREATE TABLE answer SELECT *
> FROM gps_match_distance
> LEFT JOIN gps_match_cab_reject ON
> ( gps_match_distance.call_serial_number,
> gps_match_distance.gps_cab_number ) =3D
> ( gps_match_cab_reject.call_serial_number,
> gps_match_cab_reject.gps_cab_number )
> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>
> MySQL said:
>
> #1060 - Duplicate column name 'call_serial_number'

And here is is as formally by the manual as I can see, but it still
doesn't work...

#1060 - Duplicate column name 'call_serial_number'
CREATE TABLE answer SELECT *
FROM gps_match_offer
LEFT JOIN gps_match_cab_reject ON ( gps_match_offer.gps_cab_number =3D
gps_match_cab_reject.gps_cab_number
AND gps_match_offer.call_serial_number =3D
gps_match_cab_reject.call_serial_number )
WHERE gps_match_cab_reject.call_serial_number IS NULL

On 2010-10-30 06:46, Charles wrote:
> On Oct 29, 5:04 am, Charles <ccha...@gmail.com> wrote:
>> On Oct 29, 5:00 am, Charles <ccha...@gmail.com> wrote:
>>
>> > > If either call_serial_number or gps_cab_number is nullable you may have
>> > > to add additional conditions to get the semantics right.
>>
>> > And while there is no null field in either table, one cold pray and
>> > hope (and plan) for gps_match_cab_reject - table 2 in the example - to
>> > be empty , I suppose.
>>
>> And here's the error message:
>>
>> SQL query:
>>
>> CREATE TABLE answer SELECT *
>> FROM gps_match_distance
>> LEFT JOIN gps_match_cab_reject ON
>> ( gps_match_distance.call_serial_number,
>> gps_match_distance.gps_cab_number ) =
>> ( gps_match_cab_reject.call_serial_number,
>> gps_match_cab_reject.gps_cab_number )
>> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>>
>> MySQL said:
>>
>> #1060 - Duplicate column name 'call_serial_number'
> 
> And here is is as formally by the manual as I can see, but it still
> doesn't work...
> 
> #1060 - Duplicate column name 'call_serial_number'
> CREATE TABLE answer SELECT *
> FROM gps_match_offer
> LEFT JOIN gps_match_cab_reject ON ( gps_match_offer.gps_cab_number =
> gps_match_cab_reject.gps_cab_number
> AND gps_match_offer.call_serial_number =
> gps_match_cab_reject.call_serial_number )
> WHERE gps_match_cab_reject.call_serial_number IS NULL
> 

The resulting table answer will have two columns named
call_serial_number, you will have to rename one of them. Start by
replacing * in your query. select * from ... is ok when you are looking
for sample data, but you should never use it in your code.

/Lennart

On Oct 30, 1:25=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-10-30 06:46, Charles wrote:
>
>
>
>
>
> > On Oct 29, 5:04 am, Charles <ccha...@gmail.com> wrote:
> >> On Oct 29, 5:00 am, Charles <ccha...@gmail.com> wrote:
>
> >> > > If either call_serial_number or gps_cab_number is nullable you may=
 have
> >> > > to add additional conditions to get the semantics right.
>
> >> > And while there is no null field in either table, one cold pray and
> >> > hope (and plan) for gps_match_cab_reject - table 2 in the example - =
to
> >> > be empty , I suppose.
>
> >> And here's the error message:
>
> >> SQL query:
>
> >> CREATE TABLE answer SELECT *
> >> FROM gps_match_distance
> >> LEFT JOIN gps_match_cab_reject ON
> >> ( gps_match_distance.call_serial_number,
> >> gps_match_distance.gps_cab_number ) =3D
> >> ( gps_match_cab_reject.call_serial_number,
> >> gps_match_cab_reject.gps_cab_number )
> >> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>
> >> MySQL said:
>
> >> #1060 - Duplicate column name 'call_serial_number'
>
> > And here is is as formally by the manual as I can see, but it still
> > doesn't work...
>
> > #1060 - Duplicate column name 'call_serial_number'
> > CREATE TABLE answer SELECT *
> > FROM gps_match_offer
> > LEFT JOIN gps_match_cab_reject ON ( gps_match_offer.gps_cab_number =3D
> > gps_match_cab_reject.gps_cab_number
> > AND gps_match_offer.call_serial_number =3D
> > gps_match_cab_reject.call_serial_number )
> > WHERE gps_match_cab_reject.call_serial_number IS NULL
>
> The resulting table answer will have two columns named
> call_serial_number, you will have to rename one of them. Start by
> replacing * in your query. select * from ... is ok when you are looking
> for sample data, but you should never use it in your code.
>
> /Lennart- Hide quoted text -

So cool.  This was definitely worth getting up at 2:00 a.m. and
starting up the coffee pot.  I can now identify records to be removed
from a table (set the equalities to inequalities in your snippet), and
I can also poll for records left, based on the content of a second
table.  Truncating/reinserting the records is relatively simple.

Now, for my next question.  Can this principle be applied across
multiple tables?  So  for example, I can identify/remove records from
table1 matching records in table2 with a matching relationship in
table3, with a matching relationship in table4?  Paradox and dbase
allows you to select matches across tables, so a query selecting or
removing a record can connect across any number tables to a seemingly
unrelated element.

Like:

CREATE TABLE answer
SELECT table1.field1, table1.field2, table1.field3
FROM table1 LEFT JOIN table2, table3, table4
ON (table1.field1 =3D table2.field1
AND table2.field2 =3D table3.field3
AND table3.field3 =3D table4.field4)
WHERE table2.field2 IS NULL;

Where a record could exists that meets all conditions and thus results
in the selection of a record in table1.  Let's assume so I'm not
confused (and that happens easily) that the order of table 2, table3,
and table4 after the LEFT JOIN is probably signficant.

On 2010-10-30 12:40, Charles wrote:
[...]
> Now, for my next question.  Can this principle be applied across
> multiple tables?  So  for example, I can identify/remove records from
> table1 matching records in table2 with a matching relationship in
> table3, with a matching relationship in table4?  Paradox and dbase
> allows you to select matches across tables, so a query selecting or
> removing a record can connect across any number tables to a seemingly
> unrelated element.
> 
> Like:
> 
> CREATE TABLE answer
> SELECT table1.field1, table1.field2, table1.field3
> FROM table1 LEFT JOIN table2, table3, table4
> ON (table1.field1 = table2.field1
> AND table2.field2 = table3.field3
> AND table3.field3 = table4.field4)
> WHERE table2.field2 IS NULL;
> 

in table2, table3 the "," serves as a join operator. What kind of join
is determined in the where clause. It is considered bad practise to mix
these to notations in the same query. Your query above is the same as:

SELECT table1.field1, table1.field2, table1.field3
FROM table1
LEFT JOIN table2
   ON table1.field1 = table2.field1
JOIN table3
   ON table2.field2 = table3.field3
JOIN table4
   ON table3.field3 = table4.field4
WHERE table2.field2 IS NULL;

Given your description above I guess what you want to do is:

select table1.field1, table1.field2, table1.field3
FROM table1
LEFT JOIN (
    select table2.field1
    from table2
    JOIN table3
       ON table2.field2 = table3.field3
    JOIN table4
       ON table3.field3 = table4.field4
) x
  ON table1.field1 = x.field1
WHERE x.field1 IS NULL;

If that is correct I would like to think that an not exists predicate is
easier to understand.

On Oct 30, 7:20=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-10-30 12:40, Charles wrote:
> [...]
>
>
>
>
>
> > Now, for my next question. =A0Can this principle be applied across
> > multiple tables? =A0So =A0for example, I can identify/remove records fr=
om
> > table1 matching records in table2 with a matching relationship in
> > table3, with a matching relationship in table4? =A0Paradox and dbase
> > allows you to select matches across tables, so a query selecting or
> > removing a record can connect across any number tables to a seemingly
> > unrelated element.
>
> > Like:
>
> > CREATE TABLE answer
> > SELECT table1.field1, table1.field2, table1.field3
> > FROM table1 LEFT JOIN table2, table3, table4
> > ON (table1.field1 =3D table2.field1
> > AND table2.field2 =3D table3.field3
> > AND table3.field3 =3D table4.field4)
> > WHERE table2.field2 IS NULL;
>
> in table2, table3 the "," serves as a join operator. What kind of join
> is determined in the where clause. It is considered bad practise to mix
> these to notations in the same query. Your query above is the same as:
>
> SELECT table1.field1, table1.field2, table1.field3
> FROM table1
> LEFT JOIN table2
> =A0 =A0ON table1.field1 =3D table2.field1
> JOIN table3
> =A0 =A0ON table2.field2 =3D table3.field3
> JOIN table4
> =A0 =A0ON table3.field3 =3D table4.field4
> WHERE table2.field2 IS NULL;
>
> Given your description above I guess what you want to do is:
>
> select table1.field1, table1.field2, table1.field3
> FROM table1
> LEFT JOIN (
> =A0 =A0 select table2.field1
> =A0 =A0 from table2
> =A0 =A0 JOIN table3
> =A0 =A0 =A0 =A0ON table2.field2 =3D table3.field3
> =A0 =A0 JOIN table4
> =A0 =A0 =A0 =A0ON table3.field3 =3D table4.field4
> ) x
> =A0 ON table1.field1 =3D x.field1
> WHERE x.field1 IS NULL;
>
> If that is correct I would like to think that an not exists predicate is
> easier to understand.-

I'll let you know in a couple of weeks.  I've been normalizing the
data as I go along based on the ability to draw a chain of connections
in this way across multiple tables.  If I run into an example, and I'm
sufficiently stumped, I'll holler.

I appreciate the help.  Enlightenment is always humbling <g>...

On 10/30/2010 12:46 AM, Charles wrote:
> On Oct 29, 5:04 am, Charles<ccha...@gmail.com>  wrote:
>> On Oct 29, 5:00 am, Charles<ccha...@gmail.com>  wrote:
>>
>>>> If either call_serial_number or gps_cab_number is nullable you may have
>>>> to add additional conditions to get the semantics right.
>>
>>> And while there is no null field in either table, one cold pray and
>>> hope (and plan) for gps_match_cab_reject - table 2 in the example - to
>>> be empty , I suppose.
>>
>> And here's the error message:
>>
>> SQL query:
>>
>> CREATE TABLE answer SELECT *
>> FROM gps_match_distance
>> LEFT JOIN gps_match_cab_reject ON
>> ( gps_match_distance.call_serial_number,
>> gps_match_distance.gps_cab_number ) =
>> ( gps_match_cab_reject.call_serial_number,
>> gps_match_cab_reject.gps_cab_number )
>> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>>
>> MySQL said:
>>
>> #1060 - Duplicate column name 'call_serial_number'
>
> And here is is as formally by the manual as I can see, but it still
> doesn't work...
>
> #1060 - Duplicate column name 'call_serial_number'
> CREATE TABLE answer SELECT *
> FROM gps_match_offer
> LEFT JOIN gps_match_cab_reject ON ( gps_match_offer.gps_cab_number =
> gps_match_cab_reject.gps_cab_number
> AND gps_match_offer.call_serial_number =
> gps_match_cab_reject.call_serial_number )
> WHERE gps_match_cab_reject.call_serial_number IS NULL
>

My first question is why are you creating another table for your 
results?  This is not a normal operation.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Oct 30, 12:15=A0pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> On 10/30/2010 12:46 AM, Charles wrote:
>
>
>
>
>
> > On Oct 29, 5:04 am, Charles<ccha...@gmail.com> =A0wrote:
> >> On Oct 29, 5:00 am, Charles<ccha...@gmail.com> =A0wrote:
>
> >>>> If either call_serial_number or gps_cab_number is nullable you may h=
ave
> >>>> to add additional conditions to get the semantics right.
>
> >>> And while there is no null field in either table, one cold pray and
> >>> hope (and plan) for gps_match_cab_reject - table 2 in the example - t=
o
> >>> be empty , I suppose.
>
> >> And here's the error message:
>
> >> SQL query:
>
> >> CREATE TABLE answer SELECT *
> >> FROM gps_match_distance
> >> LEFT JOIN gps_match_cab_reject ON
> >> ( gps_match_distance.call_serial_number,
> >> gps_match_distance.gps_cab_number ) =3D
> >> ( gps_match_cab_reject.call_serial_number,
> >> gps_match_cab_reject.gps_cab_number )
> >> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>
> >> MySQL said:
>
> >> #1060 - Duplicate column name 'call_serial_number'
>
> > And here is is as formally by the manual as I can see, but it still
> > doesn't work...
>
> > #1060 - Duplicate column name 'call_serial_number'
> > CREATE TABLE answer SELECT *
> > FROM gps_match_offer
> > LEFT JOIN gps_match_cab_reject ON ( gps_match_offer.gps_cab_number =3D
> > gps_match_cab_reject.gps_cab_number
> > AND gps_match_offer.call_serial_number =3D
> > gps_match_cab_reject.call_serial_number )
> > WHERE gps_match_cab_reject.call_serial_number IS NULL
>
> My first question is why are you creating another table for your
> results? =A0This is not a normal operation.

Three reasons:

1)  I'm working on an appplication I've already prototyped in Paradox,
and I'm shifting it to MySQL/php for performance reasons.

2)  It's easier for me to make a learning transition from Paradox
while I am also developing an application by keeping it table bound
until I learn what I am doing in MySQL.

3)  If it's table bound, then I can watch it work and avoid errors in
processing.

On 10/30/2010 7:29 PM, Charles wrote:
> On Oct 30, 12:15 pm, Jerry Stuckle<jstuck...@attglobal.net>  wrote:
>> On 10/30/2010 12:46 AM, Charles wrote:
>>
>>
>>
>>
>>
>>> On Oct 29, 5:04 am, Charles<ccha...@gmail.com>    wrote:
>>>> On Oct 29, 5:00 am, Charles<ccha...@gmail.com>    wrote:
>>
>>>>>> If either call_serial_number or gps_cab_number is nullable you may have
>>>>>> to add additional conditions to get the semantics right.
>>
>>>>> And while there is no null field in either table, one cold pray and
>>>>> hope (and plan) for gps_match_cab_reject - table 2 in the example - to
>>>>> be empty , I suppose.
>>
>>>> And here's the error message:
>>
>>>> SQL query:
>>
>>>> CREATE TABLE answer SELECT *
>>>> FROM gps_match_distance
>>>> LEFT JOIN gps_match_cab_reject ON
>>>> ( gps_match_distance.call_serial_number,
>>>> gps_match_distance.gps_cab_number ) =
>>>> ( gps_match_cab_reject.call_serial_number,
>>>> gps_match_cab_reject.gps_cab_number )
>>>> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>>
>>>> MySQL said:
>>
>>>> #1060 - Duplicate column name 'call_serial_number'
>>
>>> And here is is as formally by the manual as I can see, but it still
>>> doesn't work...
>>
>>> #1060 - Duplicate column name 'call_serial_number'
>>> CREATE TABLE answer SELECT *
>>> FROM gps_match_offer
>>> LEFT JOIN gps_match_cab_reject ON ( gps_match_offer.gps_cab_number =
>>> gps_match_cab_reject.gps_cab_number
>>> AND gps_match_offer.call_serial_number =
>>> gps_match_cab_reject.call_serial_number )
>>> WHERE gps_match_cab_reject.call_serial_number IS NULL
>>
>> My first question is why are you creating another table for your
>> results?  This is not a normal operation.
>
> Three reasons:
>
> 1)  I'm working on an appplication I've already prototyped in Paradox,
> and I'm shifting it to MySQL/php for performance reasons.
>
> 2)  It's easier for me to make a learning transition from Paradox
> while I am also developing an application by keeping it table bound
> until I learn what I am doing in MySQL.
>
> 3)  If it's table bound, then I can watch it work and avoid errors in
> processing.
>

You should learn how to do it the right way from the start.  You don't 
keep creating tables every time you want a result.  You get the result 
set and use it.  All the data will be available in the result set.

Don't think of this in terms of Paradox - you're using a (sort of) real 
database now.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Oct 30, 5:45=A0pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> On 10/30/2010 7:29 PM, Charles wrote:
>
>
>
>
>
> > On Oct 30, 12:15 pm, Jerry Stuckle<jstuck...@attglobal.net> =A0wrote:
> >> On 10/30/2010 12:46 AM, Charles wrote:
>
> >>> On Oct 29, 5:04 am, Charles<ccha...@gmail.com> =A0 =A0wrote:
> >>>> On Oct 29, 5:00 am, Charles<ccha...@gmail.com> =A0 =A0wrote:
>
> >>>>>> If either call_serial_number or gps_cab_number is nullable you may=
 have
> >>>>>> to add additional conditions to get the semantics right.
>
> >>>>> And while there is no null field in either table, one cold pray and
> >>>>> hope (and plan) for gps_match_cab_reject - table 2 in the example -=
 to
> >>>>> be empty , I suppose.
>
> >>>> And here's the error message:
>
> >>>> SQL query:
>
> >>>> CREATE TABLE answer SELECT *
> >>>> FROM gps_match_distance
> >>>> LEFT JOIN gps_match_cab_reject ON
> >>>> ( gps_match_distance.call_serial_number,
> >>>> gps_match_distance.gps_cab_number ) =3D
> >>>> ( gps_match_cab_reject.call_serial_number,
> >>>> gps_match_cab_reject.gps_cab_number )
> >>>> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>
> >>>> MySQL said:
>
> >>>> #1060 - Duplicate column name 'call_serial_number'
>
> >>> And here is is as formally by the manual as I can see, but it still
> >>> doesn't work...
>
> >>> #1060 - Duplicate column name 'call_serial_number'
> >>> CREATE TABLE answer SELECT *
> >>> FROM gps_match_offer
> >>> LEFT JOIN gps_match_cab_reject ON ( gps_match_offer.gps_cab_number =
=3D
> >>> gps_match_cab_reject.gps_cab_number
> >>> AND gps_match_offer.call_serial_number =3D
> >>> gps_match_cab_reject.call_serial_number )
> >>> WHERE gps_match_cab_reject.call_serial_number IS NULL
>
> >> My first question is why are you creating another table for your
> >> results? =A0This is not a normal operation.
>
> > Three reasons:
>
> > 1) =A0I'm working on an appplication I've already prototyped in Paradox=
,
> > and I'm shifting it to MySQL/php for performance reasons.
>
> > 2) =A0It's easier for me to make a learning transition from Paradox
> > while I am also developing an application by keeping it table bound
> > until I learn what I am doing in MySQL.
>
> > 3) =A0If it's table bound, then I can watch it work and avoid errors in
> > processing.
>
> You should learn how to do it the right way from the start. =A0You don't
> keep creating tables every time you want a result. =A0You get the result
> set and use it. =A0All the data will be available in the result set.
>
> Don't think of this in terms of Paradox - you're using a (sort of) real
> database now.

As soon as I am ready, and as soon as I am able to.  Right now I'm
reducing a real world process (one that's intuitive to me) down to
code  in a  language (SQL) that I don't know well enough, and I still
need to put together the user interface (in php) and untimatelyy on a
web server.  I'm more concerned with making sure the process as
reduced to code is accurate and precise.

I'm sure I'll get to the *proper* way to do this at some point, and
then all will be fine.

On Oct 30, 10:52=A0am, Charles <ccha...@gmail.com> wrote:
> On Oct 30, 7:20=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
> wrote:
>
>
>
>
>
> > On 2010-10-30 12:40, Charles wrote:
> > [...]
>
> > > Now, for my next question. =A0Can this principle be applied across
> > > multiple tables? =A0So =A0for example, I can identify/remove records =
from
> > > table1 matching records in table2 with a matching relationship in
> > > table3, with a matching relationship in table4? =A0Paradox and dbase
> > > allows you to select matches across tables, so a query selecting or
> > > removing a record can connect across any number tables to a seemingly
> > > unrelated element.
>
> > > Like:
>
> > > CREATE TABLE answer
> > > SELECT table1.field1, table1.field2, table1.field3
> > > FROM table1 LEFT JOIN table2, table3, table4
> > > ON (table1.field1 =3D table2.field1
> > > AND table2.field2 =3D table3.field3
> > > AND table3.field3 =3D table4.field4)
> > > WHERE table2.field2 IS NULL;
>
> > in table2, table3 the "," serves as a join operator. What kind of join
> > is determined in the where clause. It is considered bad practise to mix
> > these to notations in the same query. Your query above is the same as:
>
> > SELECT table1.field1, table1.field2, table1.field3
> > FROM table1
> > LEFT JOIN table2
> > =A0 =A0ON table1.field1 =3D table2.field1
> > JOIN table3
> > =A0 =A0ON table2.field2 =3D table3.field3
> > JOIN table4
> > =A0 =A0ON table3.field3 =3D table4.field4
> > WHERE table2.field2 IS NULL;
>
> > Given your description above I guess what you want to do is:
>
> > select table1.field1, table1.field2, table1.field3
> > FROM table1
> > LEFT JOIN (
> > =A0 =A0 select table2.field1
> > =A0 =A0 from table2
> > =A0 =A0 JOIN table3
> > =A0 =A0 =A0 =A0ON table2.field2 =3D table3.field3
> > =A0 =A0 JOIN table4
> > =A0 =A0 =A0 =A0ON table3.field3 =3D table4.field4
> > ) x
> > =A0 ON table1.field1 =3D x.field1
> > WHERE x.field1 IS NULL;
>
> > If that is correct I would like to think that an not exists predicate i=
s
> > easier to understand.-
>
> I'll let you know in a couple of weeks. =A0I've been normalizing the
> data as I go along based on the ability to draw a chain of connections
> in this way across multiple tables. =A0If I run into an example, and I'm
> sufficiently stumped, I'll holler.
>
> I appreciate the help. =A0Enlightenment is always humbling <g>...- Hide q=
uoted text -

And here's the example.

The objective is to compare a driver's computed (averaged) distance to
fares compared with the distance from cab to call.

The table being manipulated is the gps_match_distance table.  Fields
are:
'call_serial_number`
`gps_cab_number`
`gps_distance`

We look up in the cab_serial table to find the driver (driver number):

`cab_number`,
`fleet_number`,
`driver_number`,
`date_added`,
`time_added`,
`username_added`

Using the driver number field, we look up the driver_search_radius
field in the driver_numbers table:

`Driver_number`,
`Driver_GPS_phone_number`,
`Drive_national_ID`,
`Driver_fleet_number`,
`Driver_license_number`,
`Driver_license_jurisdiction`,
`Driver_license_expiration_date`,
`driver_search_radius`,
`date_added`,
`time_added`,
`username_added`

Such that the coding separates out as a mismatch all trips whose
gps_distance is greater than the driver_search_radius by, say, 50
percent.

Same process is repeated for geofenced location of cab, just different
tables., to eliminate trips for which cabs in general drive some x
distance plus 50 percent to get the trip.

And that's why it has to follow that I need to account for all
records, and remain table bound for now.  As this process keeps going,
the gps_match_mismatch table is accumulating all rejected records, and
the gps_match_mismatch table gets thinned out in a future step.

On Oct 31, 9:33=A0am, Charles <ccha...@gmail.com> wrote:
> On Oct 30, 10:52=A0am, Charles <ccha...@gmail.com> wrote:
>
>
>
>
>
> > On Oct 30, 7:20=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
> > wrote:
>
> > > On 2010-10-30 12:40, Charles wrote:
> > > [...]
>
> > > > Now, for my next question. =A0Can this principle be applied across
> > > > multiple tables? =A0So =A0for example, I can identify/remove record=
s from
> > > > table1 matching records in table2 with a matching relationship in
> > > > table3, with a matching relationship in table4? =A0Paradox and dbas=
e
> > > > allows you to select matches across tables, so a query selecting or
> > > > removing a record can connect across any number tables to a seeming=
ly
> > > > unrelated element.
>
> > > > Like:
>
> > > > CREATE TABLE answer
> > > > SELECT table1.field1, table1.field2, table1.field3
> > > > FROM table1 LEFT JOIN table2, table3, table4
> > > > ON (table1.field1 =3D table2.field1
> > > > AND table2.field2 =3D table3.field3
> > > > AND table3.field3 =3D table4.field4)
> > > > WHERE table2.field2 IS NULL;
>
> > > in table2, table3 the "," serves as a join operator. What kind of joi=
n
> > > is determined in the where clause. It is considered bad practise to m=
ix
> > > these to notations in the same query. Your query above is the same as=
:
>
> > > SELECT table1.field1, table1.field2, table1.field3
> > > FROM table1
> > > LEFT JOIN table2
> > > =A0 =A0ON table1.field1 =3D table2.field1
> > > JOIN table3
> > > =A0 =A0ON table2.field2 =3D table3.field3
> > > JOIN table4
> > > =A0 =A0ON table3.field3 =3D table4.field4
> > > WHERE table2.field2 IS NULL;
>
> > > Given your description above I guess what you want to do is:
>
> > > select table1.field1, table1.field2, table1.field3
> > > FROM table1
> > > LEFT JOIN (
> > > =A0 =A0 select table2.field1
> > > =A0 =A0 from table2
> > > =A0 =A0 JOIN table3
> > > =A0 =A0 =A0 =A0ON table2.field2 =3D table3.field3
> > > =A0 =A0 JOIN table4
> > > =A0 =A0 =A0 =A0ON table3.field3 =3D table4.field4
> > > ) x
> > > =A0 ON table1.field1 =3D x.field1
> > > WHERE x.field1 IS NULL;
>
> > > If that is correct I would like to think that an not exists predicate=
 is
> > > easier to understand.-
>
> > I'll let you know in a couple of weeks. =A0I've been normalizing the
> > data as I go along based on the ability to draw a chain of connections
> > in this way across multiple tables. =A0If I run into an example, and I'=
m
> > sufficiently stumped, I'll holler.
>
> > I appreciate the help. =A0Enlightenment is always humbling <g>...- Hide=
 quoted text -
>
> And here's the example.
>
> The objective is to compare a driver's computed (averaged) distance to
> fares compared with the distance from cab to call.
>
> The table being manipulated is the gps_match_distance table. =A0Fields
> are:
> 'call_serial_number`
> `gps_cab_number`
> `gps_distance`
>
> We look up in the cab_serial table to find the driver (driver number):
>
> `cab_number`,
> `fleet_number`,
> `driver_number`,
> `date_added`,
> `time_added`,
> `username_added`
>
> Using the driver number field, we look up the driver_search_radius
> field in the driver_numbers table:
>
> `Driver_number`,
> `Driver_GPS_phone_number`,
> `Drive_national_ID`,
> `Driver_fleet_number`,
> `Driver_license_number`,
> `Driver_license_jurisdiction`,
> `Driver_license_expiration_date`,
> `driver_search_radius`,
> `date_added`,
> `time_added`,
> `username_added`
>
> Such that the coding separates out as a mismatch all trips whose
> gps_distance is greater than the driver_search_radius by, say, 50
> percent.
>
> Same process is repeated for geofenced location of cab, just different
> tables., to eliminate trips for which cabs in general drive some x
> distance plus 50 percent to get the trip.
>
> And that's why it has to follow that I need to account for all
> records, and remain table bound for now. =A0As this process keeps going,
> the gps_match_mismatch table is accumulating all rejected records, and
> the gps_match_mismatch table gets thinned out in a future step.- Hide quo=
ted text -
>

This is the start that I come up with:

CREATE TABLE answer select
call_serial_number, gps_cab_number, gps_distance FROM
gps_match_distance JOIN
cab_serial_number JOIN
drivers_numbers
WHERE
gps.match_distance.gps_cab_number =3D cab_serial.cab_number AND
cab_serial.driver_number =3D driver_numbers.driver_number AND
(driver_numbers.driver_search_radius) * 1.5 >=3D
gps_match_distance.gps_distance
WHERE
cab_serial_number IS NULL;

On Oct 31, 11:09=A0am, Charles <ccha...@gmail.com> wrote:
> On Oct 31, 9:33=A0am, Charles <ccha...@gmail.com> wrote:
>
>
>
>
>
> > On Oct 30, 10:52=A0am, Charles <ccha...@gmail.com> wrote:
>
> > > On Oct 30, 7:20=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com=
>
> > > wrote:
>
> > > > On 2010-10-30 12:40, Charles wrote:
> > > > [...]
>
> > > > > Now, for my next question. =A0Can this principle be applied acros=
s
> > > > > multiple tables? =A0So =A0for example, I can identify/remove reco=
rds from
> > > > > table1 matching records in table2 with a matching relationship in
> > > > > table3, with a matching relationship in table4? =A0Paradox and db=
ase
> > > > > allows you to select matches across tables, so a query selecting =
or
> > > > > removing a record can connect across any number tables to a seemi=
ngly
> > > > > unrelated element.
>
> > > > > Like:
>
> > > > > CREATE TABLE answer
> > > > > SELECT table1.field1, table1.field2, table1.field3
> > > > > FROM table1 LEFT JOIN table2, table3, table4
> > > > > ON (table1.field1 =3D table2.field1
> > > > > AND table2.field2 =3D table3.field3
> > > > > AND table3.field3 =3D table4.field4)
> > > > > WHERE table2.field2 IS NULL;
>
> > > > in table2, table3 the "," serves as a join operator. What kind of j=
oin
> > > > is determined in the where clause. It is considered bad practise to=
 mix
> > > > these to notations in the same query. Your query above is the same =
as:
>
> > > > SELECT table1.field1, table1.field2, table1.field3
> > > > FROM table1
> > > > LEFT JOIN table2
> > > > =A0 =A0ON table1.field1 =3D table2.field1
> > > > JOIN table3
> > > > =A0 =A0ON table2.field2 =3D table3.field3
> > > > JOIN table4
> > > > =A0 =A0ON table3.field3 =3D table4.field4
> > > > WHERE table2.field2 IS NULL;
>
> > > > Given your description above I guess what you want to do is:
>
> > > > select table1.field1, table1.field2, table1.field3
> > > > FROM table1
> > > > LEFT JOIN (
> > > > =A0 =A0 select table2.field1
> > > > =A0 =A0 from table2
> > > > =A0 =A0 JOIN table3
> > > > =A0 =A0 =A0 =A0ON table2.field2 =3D table3.field3
> > > > =A0 =A0 JOIN table4
> > > > =A0 =A0 =A0 =A0ON table3.field3 =3D table4.field4
> > > > ) x
> > > > =A0 ON table1.field1 =3D x.field1
> > > > WHERE x.field1 IS NULL;
>
> > > > If that is correct I would like to think that an not exists predica=
te is
> > > > easier to understand.-
>
> > > I'll let you know in a couple of weeks. =A0I've been normalizing the
> > > data as I go along based on the ability to draw a chain of connection=
s
> > > in this way across multiple tables. =A0If I run into an example, and =
I'm
> > > sufficiently stumped, I'll holler.
>
> > > I appreciate the help. =A0Enlightenment is always humbling <g>...- Hi=
de quoted text -
>
> > And here's the example.
>
> > The objective is to compare a driver's computed (averaged) distance to
> > fares compared with the distance from cab to call.
>
> > The table being manipulated is the gps_match_distance table. =A0Fields
> > are:
> > 'call_serial_number`
> > `gps_cab_number`
> > `gps_distance`
>
> > We look up in the cab_serial table to find the driver (driver number):
>
> > `cab_number`,
> > `fleet_number`,
> > `driver_number`,
> > `date_added`,
> > `time_added`,
> > `username_added`
>
> > Using the driver number field, we look up the driver_search_radius
> > field in the driver_numbers table:
>
> > `Driver_number`,
> > `Driver_GPS_phone_number`,
> > `Drive_national_ID`,
> > `Driver_fleet_number`,
> > `Driver_license_number`,
> > `Driver_license_jurisdiction`,
> > `Driver_license_expiration_date`,
> > `driver_search_radius`,
> > `date_added`,
> > `time_added`,
> > `username_added`
>
> > Such that the coding separates out as a mismatch all trips whose
> > gps_distance is greater than the driver_search_radius by, say, 50
> > percent.
>
> > Same process is repeated for geofenced location of cab, just different
> > tables., to eliminate trips for which cabs in general drive some x
> > distance plus 50 percent to get the trip.
>
> > And that's why it has to follow that I need to account for all
> > records, and remain table bound for now. =A0As this process keeps going=
,
> > the gps_match_mismatch table is accumulating all rejected records, and
> > the gps_match_mismatch table gets thinned out in a future step.- Hide q=
uoted text -
>
> This is the start that I come up with:
>
Try this - it's closer.
CREATE TABLE answer SELECT gps_match_distance.call_serial_number,
gps_match_distance.gps_cab_number, gps_match_distance.gps_distance
FROM gps_match_distance
LEFT JOIN gps_match_cab_reject
JOIN cab_serial
JOIN driver_numbers ON (

gps_match_distance.gps_cab_number =3D cab_serial.gps_cab_number
AND cab.serial.driver_number =3D driver_numbers.driver_number
AND driver_numbers.driver_search_radius >
gps_match_distance.gps_distance
)
WHERE gps_match_cab_reject.call_serial_number IS NULL ;



MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'WHERE gps_match_cab_reject.call_serial_number IS NULL' at line
16

On 2010-10-31 22:16, Charles wrote:
> On Oct 31, 11:09 am, Charles <ccha...@gmail.com> wrote:
>> On Oct 31, 9:33 am, Charles <ccha...@gmail.com> wrote:
>>
>>
>>
>>
>>
>> > On Oct 30, 10:52 am, Charles <ccha...@gmail.com> wrote:
>>
>> > > On Oct 30, 7:20 am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
>> > > wrote:
>>
>> > > > On 2010-10-30 12:40, Charles wrote:
>> > > > [...]
>>
>> > > > > Now, for my next question.  Can this principle be applied across
>> > > > > multiple tables?  So  for example, I can identify/remove records from
>> > > > > table1 matching records in table2 with a matching relationship in
>> > > > > table3, with a matching relationship in table4?  Paradox and dbase
>> > > > > allows you to select matches across tables, so a query selecting or
>> > > > > removing a record can connect across any number tables to a seemingly
>> > > > > unrelated element.
>>
>> > > > > Like:
>>
>> > > > > CREATE TABLE answer
>> > > > > SELECT table1.field1, table1.field2, table1.field3
>> > > > > FROM table1 LEFT JOIN table2, table3, table4
>> > > > > ON (table1.field1 = table2.field1
>> > > > > AND table2.field2 = table3.field3
>> > > > > AND table3.field3 = table4.field4)
>> > > > > WHERE table2.field2 IS NULL;
>>
>> > > > in table2, table3 the "," serves as a join operator. What kind of join
>> > > > is determined in the where clause. It is considered bad practise to mix
>> > > > these to notations in the same query. Your query above is the same as:
>>
>> > > > SELECT table1.field1, table1.field2, table1.field3
>> > > > FROM table1
>> > > > LEFT JOIN table2
>> > > >    ON table1.field1 = table2.field1
>> > > > JOIN table3
>> > > >    ON table2.field2 = table3.field3
>> > > > JOIN table4
>> > > >    ON table3.field3 = table4.field4
>> > > > WHERE table2.field2 IS NULL;
>>
>> > > > Given your description above I guess what you want to do is:
>>
>> > > > select table1.field1, table1.field2, table1.field3
>> > > > FROM table1
>> > > > LEFT JOIN (
>> > > >     select table2.field1
>> > > >     from table2
>> > > >     JOIN table3
>> > > >        ON table2.field2 = table3.field3
>> > > >     JOIN table4
>> > > >        ON table3.field3 = table4.field4
>> > > > ) x
>> > > >   ON table1.field1 = x.field1
>> > > > WHERE x.field1 IS NULL;
>>
>> > > > If that is correct I would like to think that an not exists predicate is
>> > > > easier to understand.-
>>
>> > > I'll let you know in a couple of weeks.  I've been normalizing the
>> > > data as I go along based on the ability to draw a chain of connections
>> > > in this way across multiple tables.  If I run into an example, and I'm
>> > > sufficiently stumped, I'll holler.
>>
>> > > I appreciate the help.  Enlightenment is always humbling <g>...- Hide quoted text -
>>
>> > And here's the example.
>>
>> > The objective is to compare a driver's computed (averaged) distance to
>> > fares compared with the distance from cab to call.
>>
>> > The table being manipulated is the gps_match_distance table.  Fields
>> > are:
>> > 'call_serial_number`
>> > `gps_cab_number`
>> > `gps_distance`
>>
>> > We look up in the cab_serial table to find the driver (driver number):
>>
>> > `cab_number`,
>> > `fleet_number`,
>> > `driver_number`,
>> > `date_added`,
>> > `time_added`,
>> > `username_added`
>>
>> > Using the driver number field, we look up the driver_search_radius
>> > field in the driver_numbers table:
>>
>> > `Driver_number`,
>> > `Driver_GPS_phone_number`,
>> > `Drive_national_ID`,
>> > `Driver_fleet_number`,
>> > `Driver_license_number`,
>> > `Driver_license_jurisdiction`,
>> > `Driver_license_expiration_date`,
>> > `driver_search_radius`,
>> > `date_added`,
>> > `time_added`,
>> > `username_added`
>>
>> > Such that the coding separates out as a mismatch all trips whose
>> > gps_distance is greater than the driver_search_radius by, say, 50
>> > percent.
>>
>> > Same process is repeated for geofenced location of cab, just different
>> > tables., to eliminate trips for which cabs in general drive some x
>> > distance plus 50 percent to get the trip.
>>
>> > And that's why it has to follow that I need to account for all
>> > records, and remain table bound for now.  As this process keeps going,
>> > the gps_match_mismatch table is accumulating all rejected records, and
>> > the gps_match_mismatch table gets thinned out in a future step.- Hide quoted text -
>>
>> This is the start that I come up with:
>>
> Try this - it's closer.
> CREATE TABLE answer SELECT gps_match_distance.call_serial_number,
> gps_match_distance.gps_cab_number, gps_match_distance.gps_distance
> FROM gps_match_distance
> LEFT JOIN gps_match_cab_reject
> JOIN cab_serial
> JOIN driver_numbers ON (
> 
> gps_match_distance.gps_cab_number = cab_serial.gps_cab_number
> AND cab.serial.driver_number = driver_numbers.driver_number
> AND driver_numbers.driver_search_radius >
> gps_match_distance.gps_distance
> )
> WHERE gps_match_cab_reject.call_serial_number IS NULL ;
> 
> 
> 
> MySQL said:
> 
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'WHERE gps_match_cab_reject.call_serial_number IS NULL' at line
> 16
> 

I haven't looked into your business problem yet, but every join clause
should have an on associated with it (except for cross join). Even if
mysql accepts deviations from what's standard, the meaning of a such
queries are often muddy. I would start by filling out:

SELECT gps_match_distance.call_serial_number,
       gps_match_distance.gps_cab_number,
       gps_match_distance.gps_distance
FROM gps_match_distance
LEFT JOIN gps_match_cab_reject
    ON ...
JOIN cab_serial
    ON ...
JOIN driver_numbers ON
    ON ...
WHERE gps_match_cab_reject.call_serial_number is null

In which order do you want the join's to happen?


/Lennart

On Oct 31, 3:41=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-10-31 22:16, Charles wrote:
>
>
>
>
>
> > On Oct 31, 11:09 am, Charles <ccha...@gmail.com> wrote:
> >> On Oct 31, 9:33 am, Charles <ccha...@gmail.com> wrote:
>
> >> > On Oct 30, 10:52 am, Charles <ccha...@gmail.com> wrote:
>
> >> > > On Oct 30, 7:20 am, Lennart Jonsson <erik.lennart.jons...@gmail.co=
m>
> >> > > wrote:
>
> >> > > > On 2010-10-30 12:40, Charles wrote:
> >> > > > [...]
>
> >> > > > > Now, for my next question. =A0Can this principle be applied ac=
ross
> >> > > > > multiple tables? =A0So =A0for example, I can identify/remove r=
ecords from
> >> > > > > table1 matching records in table2 with a matching relationship=
 in
> >> > > > > table3, with a matching relationship in table4? =A0Paradox and=
 dbase
> >> > > > > allows you to select matches across tables, so a query selecti=
ng or
> >> > > > > removing a record can connect across any number tables to a se=
emingly
> >> > > > > unrelated element.
>
> >> > > > > Like:
>
> >> > > > > CREATE TABLE answer
> >> > > > > SELECT table1.field1, table1.field2, table1.field3
> >> > > > > FROM table1 LEFT JOIN table2, table3, table4
> >> > > > > ON (table1.field1 =3D table2.field1
> >> > > > > AND table2.field2 =3D table3.field3
> >> > > > > AND table3.field3 =3D table4.field4)
> >> > > > > WHERE table2.field2 IS NULL;
>
> >> > > > in table2, table3 the "," serves as a join operator. What kind o=
f join
> >> > > > is determined in the where clause. It is considered bad practise=
 to mix
> >> > > > these to notations in the same query. Your query above is the sa=
me as:
>
> >> > > > SELECT table1.field1, table1.field2, table1.field3
> >> > > > FROM table1
> >> > > > LEFT JOIN table2
> >> > > > =A0 =A0ON table1.field1 =3D table2.field1
> >> > > > JOIN table3
> >> > > > =A0 =A0ON table2.field2 =3D table3.field3
> >> > > > JOIN table4
> >> > > > =A0 =A0ON table3.field3 =3D table4.field4
> >> > > > WHERE table2.field2 IS NULL;
>
> >> > > > Given your description above I guess what you want to do is:
>
> >> > > > select table1.field1, table1.field2, table1.field3
> >> > > > FROM table1
> >> > > > LEFT JOIN (
> >> > > > =A0 =A0 select table2.field1
> >> > > > =A0 =A0 from table2
> >> > > > =A0 =A0 JOIN table3
> >> > > > =A0 =A0 =A0 =A0ON table2.field2 =3D table3.field3
> >> > > > =A0 =A0 JOIN table4
> >> > > > =A0 =A0 =A0 =A0ON table3.field3 =3D table4.field4
> >> > > > ) x
> >> > > > =A0 ON table1.field1 =3D x.field1
> >> > > > WHERE x.field1 IS NULL;
>
> >> > > > If that is correct I would like to think that an not exists pred=
icate is
> >> > > > easier to understand.-
>
> >> > > I'll let you know in a couple of weeks. =A0I've been normalizing t=
he
> >> > > data as I go along based on the ability to draw a chain of connect=
ions
> >> > > in this way across multiple tables. =A0If I run into an example, a=
nd I'm
> >> > > sufficiently stumped, I'll holler.
>
> >> > > I appreciate the help. =A0Enlightenment is always humbling <g>...-=
 Hide quoted text -
>
> >> > And here's the example.
>
> >> > The objective is to compare a driver's computed (averaged) distance =
to
> >> > fares compared with the distance from cab to call.
>
> >> > The table being manipulated is the gps_match_distance table. =A0Fiel=
ds
> >> > are:
> >> > 'call_serial_number`
> >> > `gps_cab_number`
> >> > `gps_distance`
>
> >> > We look up in the cab_serial table to find the driver (driver number=
):
>
> >> > `cab_number`,
> >> > `fleet_number`,
> >> > `driver_number`,
> >> > `date_added`,
> >> > `time_added`,
> >> > `username_added`
>
> >> > Using the driver number field, we look up the driver_search_radius
> >> > field in the driver_numbers table:
>
> >> > `Driver_number`,
> >> > `Driver_GPS_phone_number`,
> >> > `Drive_national_ID`,
> >> > `Driver_fleet_number`,
> >> > `Driver_license_number`,
> >> > `Driver_license_jurisdiction`,
> >> > `Driver_license_expiration_date`,
> >> > `driver_search_radius`,
> >> > `date_added`,
> >> > `time_added`,
> >> > `username_added`
>
> >> > Such that the coding separates out as a mismatch all trips whose
> >> > gps_distance is greater than the driver_search_radius by, say, 50
> >> > percent.
>
> >> > Same process is repeated for geofenced location of cab, just differe=
nt
> >> > tables., to eliminate trips for which cabs in general drive some x
> >> > distance plus 50 percent to get the trip.
>
> >> > And that's why it has to follow that I need to account for all
> >> > records, and remain table bound for now. =A0As this process keeps go=
ing,
> >> > the gps_match_mismatch table is accumulating all rejected records, a=
nd
> >> > the gps_match_mismatch table gets thinned out in a future step.- Hid=
e quoted text -
>
> >> This is the start that I come up with:
>
> > Try this - it's closer.
> > CREATE TABLE answer SELECT gps_match_distance.call_serial_number,
> > gps_match_distance.gps_cab_number, gps_match_distance.gps_distance
> > FROM gps_match_distance
> > LEFT JOIN gps_match_cab_reject
> > JOIN cab_serial
> > JOIN driver_numbers ON (
>
> > gps_match_distance.gps_cab_number =3D cab_serial.gps_cab_number
> > AND cab.serial.driver_number =3D driver_numbers.driver_number
> > AND driver_numbers.driver_search_radius >
> > gps_match_distance.gps_distance
> > )
> > WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>
> > MySQL said:
>
> > #1064 - You have an error in your SQL syntax; check the manual that
> > corresponds to your MySQL server version for the right syntax to use
> > near 'WHERE gps_match_cab_reject.call_serial_number IS NULL' at line
> > 16
>
> I haven't looked into your business problem yet, but every join clause
> should have an on associated with it (except for cross join). Even if
> mysql accepts deviations from what's standard, the meaning of a such
> queries are often muddy. I would start by filling out:
>
> SELECT gps_match_distance.call_serial_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_cab_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_distance
> FROM gps_match_distance
> LEFT JOIN gps_match_cab_reject
> =A0 =A0 ON ...
> JOIN cab_serial
> =A0 =A0 ON ...
> JOIN driver_numbers ON
> =A0 =A0 ON ...
> WHERE gps_match_cab_reject.call_serial_number is null
>
> In which order do you want the join's to happen?
>

In the order as the matchup happens, so that I understand the order in
which this is happening:
1)  Cab_number from gps_match_distance, paired up with cab_number in
cab_serial table.
2)  Same record in cab_serial table, driver number matched up with
driver number in driver_numbers table
3)  Same record in drivers_numbers table, return driver_search_radius
for comparison to gps_match_distance.

The result set contains those records in gps_match_distance that are
greater than the driver_search_radius.  I remove those from the
gps_match_radius table (and the gps_match_distance table has further
processing to go), reduce the result table down to trip number, and
add the trip number to the gps_match_mismatch for later processing.

On Oct 31, 3:41=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-10-31 22:16, Charles wrote:
>
>
>
>
>
> > On Oct 31, 11:09 am, Charles <ccha...@gmail.com> wrote:
> >> On Oct 31, 9:33 am, Charles <ccha...@gmail.com> wrote:
>
> >> > On Oct 30, 10:52 am, Charles <ccha...@gmail.com> wrote:
>
> >> > > On Oct 30, 7:20 am, Lennart Jonsson <erik.lennart.jons...@gmail.co=
m>
> >> > > wrote:
>
> >> > > > On 2010-10-30 12:40, Charles wrote:
> >> > > > [...]
>
> >> > > > > Now, for my next question. =A0Can this principle be applied ac=
ross
> >> > > > > multiple tables? =A0So =A0for example, I can identify/remove r=
ecords from
> >> > > > > table1 matching records in table2 with a matching relationship=
 in
> >> > > > > table3, with a matching relationship in table4? =A0Paradox and=
 dbase
> >> > > > > allows you to select matches across tables, so a query selecti=
ng or
> >> > > > > removing a record can connect across any number tables to a se=
emingly
> >> > > > > unrelated element.
>
> >> > > > > Like:
>
> >> > > > > CREATE TABLE answer
> >> > > > > SELECT table1.field1, table1.field2, table1.field3
> >> > > > > FROM table1 LEFT JOIN table2, table3, table4
> >> > > > > ON (table1.field1 =3D table2.field1
> >> > > > > AND table2.field2 =3D table3.field3
> >> > > > > AND table3.field3 =3D table4.field4)
> >> > > > > WHERE table2.field2 IS NULL;
>
> >> > > > in table2, table3 the "," serves as a join operator. What kind o=
f join
> >> > > > is determined in the where clause. It is considered bad practise=
 to mix
> >> > > > these to notations in the same query. Your query above is the sa=
me as:
>
> >> > > > SELECT table1.field1, table1.field2, table1.field3
> >> > > > FROM table1
> >> > > > LEFT JOIN table2
> >> > > > =A0 =A0ON table1.field1 =3D table2.field1
> >> > > > JOIN table3
> >> > > > =A0 =A0ON table2.field2 =3D table3.field3
> >> > > > JOIN table4
> >> > > > =A0 =A0ON table3.field3 =3D table4.field4
> >> > > > WHERE table2.field2 IS NULL;
>
> >> > > > Given your description above I guess what you want to do is:
>
> >> > > > select table1.field1, table1.field2, table1.field3
> >> > > > FROM table1
> >> > > > LEFT JOIN (
> >> > > > =A0 =A0 select table2.field1
> >> > > > =A0 =A0 from table2
> >> > > > =A0 =A0 JOIN table3
> >> > > > =A0 =A0 =A0 =A0ON table2.field2 =3D table3.field3
> >> > > > =A0 =A0 JOIN table4
> >> > > > =A0 =A0 =A0 =A0ON table3.field3 =3D table4.field4
> >> > > > ) x
> >> > > > =A0 ON table1.field1 =3D x.field1
> >> > > > WHERE x.field1 IS NULL;
>
> >> > > > If that is correct I would like to think that an not exists pred=
icate is
> >> > > > easier to understand.-
>
> >> > > I'll let you know in a couple of weeks. =A0I've been normalizing t=
he
> >> > > data as I go along based on the ability to draw a chain of connect=
ions
> >> > > in this way across multiple tables. =A0If I run into an example, a=
nd I'm
> >> > > sufficiently stumped, I'll holler.
>
> >> > > I appreciate the help. =A0Enlightenment is always humbling <g>...-=
 Hide quoted text -
>
> >> > And here's the example.
>
> >> > The objective is to compare a driver's computed (averaged) distance =
to
> >> > fares compared with the distance from cab to call.
>
> >> > The table being manipulated is the gps_match_distance table. =A0Fiel=
ds
> >> > are:
> >> > 'call_serial_number`
> >> > `gps_cab_number`
> >> > `gps_distance`
>
> >> > We look up in the cab_serial table to find the driver (driver number=
):
>
> >> > `cab_number`,
> >> > `fleet_number`,
> >> > `driver_number`,
> >> > `date_added`,
> >> > `time_added`,
> >> > `username_added`
>
> >> > Using the driver number field, we look up the driver_search_radius
> >> > field in the driver_numbers table:
>
> >> > `Driver_number`,
> >> > `Driver_GPS_phone_number`,
> >> > `Drive_national_ID`,
> >> > `Driver_fleet_number`,
> >> > `Driver_license_number`,
> >> > `Driver_license_jurisdiction`,
> >> > `Driver_license_expiration_date`,
> >> > `driver_search_radius`,
> >> > `date_added`,
> >> > `time_added`,
> >> > `username_added`
>
> >> > Such that the coding separates out as a mismatch all trips whose
> >> > gps_distance is greater than the driver_search_radius by, say, 50
> >> > percent.
>
> >> > Same process is repeated for geofenced location of cab, just differe=
nt
> >> > tables., to eliminate trips for which cabs in general drive some x
> >> > distance plus 50 percent to get the trip.
>
> >> > And that's why it has to follow that I need to account for all
> >> > records, and remain table bound for now. =A0As this process keeps go=
ing,
> >> > the gps_match_mismatch table is accumulating all rejected records, a=
nd
> >> > the gps_match_mismatch table gets thinned out in a future step.- Hid=
e quoted text -
>
> >> This is the start that I come up with:
>
> > Try this - it's closer.
> > CREATE TABLE answer SELECT gps_match_distance.call_serial_number,
> > gps_match_distance.gps_cab_number, gps_match_distance.gps_distance
> > FROM gps_match_distance
> > LEFT JOIN gps_match_cab_reject
> > JOIN cab_serial
> > JOIN driver_numbers ON (
>
> > gps_match_distance.gps_cab_number =3D cab_serial.gps_cab_number
> > AND cab.serial.driver_number =3D driver_numbers.driver_number
> > AND driver_numbers.driver_search_radius >
> > gps_match_distance.gps_distance
> > )
> > WHERE gps_match_cab_reject.call_serial_number IS NULL ;
>
> > MySQL said:
>
> > #1064 - You have an error in your SQL syntax; check the manual that
> > corresponds to your MySQL server version for the right syntax to use
> > near 'WHERE gps_match_cab_reject.call_serial_number IS NULL' at line
> > 16
>
> I haven't looked into your business problem yet, but every join clause
> should have an on associated with it (except for cross join). Even if
> mysql accepts deviations from what's standard, the meaning of a such
> queries are often muddy. I would start by filling out:
>
> SELECT gps_match_distance.call_serial_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_cab_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_distance
> FROM gps_match_distance
> LEFT JOIN gps_match_cab_reject
> =A0 =A0 ON ...
> JOIN cab_serial
> =A0 =A0 ON ...
> JOIN driver_numbers ON
> =A0 =A0 ON ...
> WHERE gps_match_cab_reject.call_serial_number is null
>
> In which order do you want the join's to happen?


Or is this what you are describing?


DROP TABLE IF EXISTS answer;

CREATE TABLE answer
SELECT gps_match_distance.call_serial_number,
       gps_match_distance.gps_cab_number,
       gps_match_distance.gps_distance
FROM gps_match_distance
LEFT JOIN cab_serial
    ON gps_match_distance.gps_cab_number =3D cab_serial.cab_number
JOIN driver_numbers
     ON cab_serial.driver_number =3D driver_numbers.driver_number
     AND gps_match_distance.gps_distance >
(driver_numbers.driver_search_radius * 1.5)
WHERE cab_serial.cab_number IS NULL;

On 2010-11-01 01:14, Charles wrote:
[...]
>> SELECT gps_match_distance.call_serial_number,
>>        gps_match_distance.gps_cab_number,
>>        gps_match_distance.gps_distance
>> FROM gps_match_distance
>> LEFT JOIN gps_match_cab_reject
>>     ON ...
>> JOIN cab_serial
>>     ON ...
>> JOIN driver_numbers ON
>>     ON ...
>> WHERE gps_match_cab_reject.call_serial_number is null
>>
>> In which order do you want the join's to happen?
>>
> 
> In the order as the matchup happens, so that I understand the order in
> which this is happening:
> 1)  Cab_number from gps_match_distance, paired up with cab_number in
> cab_serial table.
> 2)  Same record in cab_serial table, driver number matched up with
> driver number in driver_numbers table
> 3)  Same record in drivers_numbers table, return driver_search_radius
> for comparison to gps_match_distance.
> 
> The result set contains those records in gps_match_distance that are
> greater than the driver_search_radius.  I remove those from the
> gps_match_radius table (and the gps_match_distance table has further
> processing to go), reduce the result table down to trip number, and
> add the trip number to the gps_match_mismatch for later processing.


In a hurry but my guess is that you would first like a relation (table)
that consists of:

SELECT gps_match_distance.call_serial_number,
       gps_match_distance.gps_cab_number,
       gps_match_distance.gps_distance,
       ...
FROM gps_match_distance
JOIN cab_serial
     ON ...
JOIN driver_numbers ON
     ON ...

from there you would like to subtract gps_match_cab_reject which can be
implemented either via not exists or an outer join.

SELECT gps_match_distance.call_serial_number,
       gps_match_distance.gps_cab_number,
       gps_match_distance.gps_distance,
       ...
FROM gps_match_distance
JOIN cab_serial
     ON ...
JOIN driver_numbers ON
     ON ...
LEFT JOIN gps_match_cab_reject
     ON ...
WHERE gps_match_cab_reject.call_serial_number is null


One reflection I made over the years is that we (as developers) are to
eager to start implementing queries. IMO it is better to formulate the
problem in terms of sets and predicates, and first after that start
implementing them.


/Lennart

On Nov 1, 1:01=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-11-01 01:14, Charles wrote:
> [...]
>
>
>
>
>
> >> SELECT gps_match_distance.call_serial_number,
> >> =A0 =A0 =A0 =A0gps_match_distance.gps_cab_number,
> >> =A0 =A0 =A0 =A0gps_match_distance.gps_distance
> >> FROM gps_match_distance
> >> LEFT JOIN gps_match_cab_reject
> >> =A0 =A0 ON ...
> >> JOIN cab_serial
> >> =A0 =A0 ON ...
> >> JOIN driver_numbers ON
> >> =A0 =A0 ON ...
> >> WHERE gps_match_cab_reject.call_serial_number is null
>
> >> In which order do you want the join's to happen?
>
> > In the order as the matchup happens, so that I understand the order in
> > which this is happening:
> > 1) =A0Cab_number from gps_match_distance, paired up with cab_number in
> > cab_serial table.
> > 2) =A0Same record in cab_serial table, driver number matched up with
> > driver number in driver_numbers table
> > 3) =A0Same record in drivers_numbers table, return driver_search_radius
> > for comparison to gps_match_distance.
>
> > The result set contains those records in gps_match_distance that are
> > greater than the driver_search_radius. =A0I remove those from the
> > gps_match_radius table (and the gps_match_distance table has further
> > processing to go), reduce the result table down to trip number, and
> > add the trip number to the gps_match_mismatch for later processing.
>
> In a hurry but my guess is that you would first like a relation (table)
> that consists of:
>
> SELECT gps_match_distance.call_serial_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_cab_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_distance,
> =A0 =A0 =A0 =A0...
> FROM gps_match_distance
> JOIN cab_serial
> =A0 =A0 =A0ON ...
> JOIN driver_numbers ON
> =A0 =A0 =A0ON ...
>
> from there you would like to subtract gps_match_cab_reject which can be
> implemented either via not exists or an outer join.
>
> SELECT gps_match_distance.call_serial_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_cab_number,
> =A0 =A0 =A0 =A0gps_match_distance.gps_distance,
> =A0 =A0 =A0 =A0...
> FROM gps_match_distance
> JOIN cab_serial
> =A0 =A0 =A0ON ...
> JOIN driver_numbers ON
> =A0 =A0 =A0ON ...
> LEFT JOIN gps_match_cab_reject
> =A0 =A0 =A0ON ...
> WHERE gps_match_cab_reject.call_serial_number is null
>
> One reflection I made over the years is that we (as developers) are to
> eager to start implementing queries. IMO it is better to formulate the
> problem in terms of sets and predicates, and first after that start
> implementing them.

Correct, and thank you.

Two observations *I've* made over the years of working with developing
and/or fixing different applications is that normalizing data is *not*
normal to most developers, and not defining the problem sufficiently
before rushing to code an answer *is* common.  Not good practice, but
common.