More questions on SELECT using multiple tables

#1

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.

#2

On 10/28/2010 10:22 PM, Charles wrote:

Quote:

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 (AT) attglobal (DOT) net
==================

#3

On Oct 28, 7:54*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

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.

#4

On 2010-10-29 09:25, Charles wrote:

Quote:

On Oct 28, 7:54 pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) 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

#5

El 29/10/2010 9:25, Charles escribió/wrote:

Quote:

On Oct 28, 7:54 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) 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
--

#6

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

Quote:

On 2010-10-29 09:25, Charles wrote:

On Oct 28, 7:54 pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) 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.

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".

#7

Quote:

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.

#8

On Oct 29, 5:00*am, Charles <ccha... (AT) gmail (DOT) com> wrote:

Quote:

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'

#9

On 2010-10-29 13:36, Charles wrote:
[...]

Quote:

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

#10

On Oct 29, 5:22*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

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.

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?