More questions on SELECT using multiple tables

#21

On Oct 30, 10:52*am, Charles <ccha... (AT) gmail (DOT) com> wrote:

Quote:

On Oct 30, 7:20*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) 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.

#22

On Oct 31, 9:33*am, Charles <ccha... (AT) gmail (DOT) com> wrote:

Quote:

On Oct 30, 10:52*am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 7:20*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) 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 predicateis
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>...- Hidequoted 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:

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 = cab_serial.cab_number AND
cab_serial.driver_number = driver_numbers.driver_number AND
(driver_numbers.driver_search_radius) * 1.5 >=
gps_match_distance.gps_distance
WHERE
cab_serial_number IS NULL;

#23

On Oct 31, 11:09*am, Charles <ccha... (AT) gmail (DOT) com> wrote:

Quote:

On Oct 31, 9:33*am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 10:52*am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 7:20*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) 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 tomix
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

#24

On 2010-10-31 22:16, Charles wrote:

Quote:

On Oct 31, 11:09 am, Charles <ccha... (AT) gmail (DOT) com> wrote:
On Oct 31, 9:33 am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 10:52 am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 7:20 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) 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

#25

On Oct 31, 3:41*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 2010-10-31 22:16, Charles wrote:

On Oct 31, 11:09 am, Charles <ccha... (AT) gmail (DOT) com> wrote:
On Oct 31, 9:33 am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 10:52 am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 7:20 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) 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 relationshipin
table3, with a matching relationship in table4? *Paradox anddbase
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 practiseto 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?

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.

#26

On Oct 31, 3:41*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 2010-10-31 22:16, Charles wrote:

On Oct 31, 11:09 am, Charles <ccha... (AT) gmail (DOT) com> wrote:
On Oct 31, 9:33 am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 10:52 am, Charles <ccha... (AT) gmail (DOT) com> wrote:

On Oct 30, 7:20 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) 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 relationshipin
table3, with a matching relationship in table4? *Paradox anddbase
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 practiseto 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?

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 = cab_serial.cab_number
JOIN driver_numbers
ON cab_serial.driver_number = driver_numbers.driver_number
AND gps_match_distance.gps_distance >
(driver_numbers.driver_search_radius * 1.5)
WHERE cab_serial.cab_number IS NULL;

#27

On 2010-11-01 01:14, Charles wrote:
[...]

Quote:

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

#28

On Nov 1, 1:01*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

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.

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

More questions on SELECT using multiple tables

comp.databases.mysql comp.databases.mysql

Re: More questions on SELECT using multiple tables - 10-31-2010 , 10:33 AM

Re: More questions on SELECT using multiple tables - 10-31-2010 , 12:09 PM

Re: More questions on SELECT using multiple tables - 10-31-2010 , 03:16 PM

Re: More questions on SELECT using multiple tables - 10-31-2010 , 04:41 PM

Re: More questions on SELECT using multiple tables - 10-31-2010 , 06:14 PM

Re: More questions on SELECT using multiple tables - 10-31-2010 , 06:55 PM

Re: More questions on SELECT using multiple tables - 11-01-2010 , 02:01 AM

Re: More questions on SELECT using multiple tables - 11-01-2010 , 06:41 AM