Programming with dBASE

Author

Select...

2006-12-02, 5:13 am

// Suppose I have the following two tables:
// t1 and t2 have the same fields: fruits and quantity

if file("t1.dbf")
delete file "t1.dbf"
endif
create table "t1.dbf" (fruits char(10), quantity numeric(2))
insert into t1 values ("apples", 2)
insert into t1 values ("oranges", 5)
insert into t1 values ("appricots", 3)
insert into t1 values ("plumbs", 6)
insert into t1 values ("melons", 7)

if file("t2.dbf")
delete file "t2.dbf"
endif
create table "t2.dbf" (fruits char(10), quantity numeric(2))
insert into t2 values ("bananas", 9)
insert into t2 values ("apples", 2)
insert into t2 values ("oranges", 10)
insert into t2 values ("appricots", 2)
insert into t2 values ("plumbs", 5)
insert into t2 values ("melons", 9)
insert into t2 values ("grapes", 5)

/*
Now I wish to get the following list out of the two tables:
appricots 1
bananas 9
grapes 5
melons -2
oranges -5
plumbs 1

I.e., I want to subtract the values of table2 from table1
(if they exist in table1, otherwise just add them to the list).
In case both values are the same
(as it is the case with the apples in the example - 2 each)
they would cancel each other and should not appear on
the final list.
Should table1 have values not in table2 just add them
to the list as well.

Can anyone help with an SQL SELECT...? if it is possible...

SELECT t1.fruits, t1.quantity FROM t1 t2 WHERE t1.fruits = t2.fruits.....

Thank you
Carlos
*/

Roland Wingerter

2006-12-02, 7:14 pm

Carlos wrote

> // Suppose I have the following two tables:
> // t1 and t2 have the same fields: fruits and quantity
>
> Can anyone help with an SQL SELECT...? if it is possible...
>
> SELECT t1.fruits, t1.quantity FROM t1 t2 WHERE t1.fruits = t2.fruits.....
------
Try this:

SELECT t1.fruits, cast(t1.quantity-t2.quantity as integer) as quantity ;
FROM t1 ;
LEFT JOIN t2 ON (t1.fruits = t2.fruits) ;
WHERE (t1.quantity-t2.quantity) <> 0 ;
UNION ;
SELECT t2.fruits, cast(t2.quantity as integer) as quantity ;
FROM t2 ;
WHERE t2.fruits not in (select t1.fruits from t1) ;
ORDER BY fruits

Roland

2006-12-04, 5:17 am

> SELECT t1.fruits, cast(t1.quantity-t2.quantity as integer) as quantity ;
> FROM t1 ;
> LEFT JOIN t2 ON (t1.fruits = t2.fruits) ;
> WHERE (t1.quantity-t2.quantity) <> 0 ;
> UNION ;
> SELECT t2.fruits, cast(t2.quantity as integer) as quantity ;
> FROM t2 ;
> WHERE t2.fruits not in (select t1.fruits from t1) ;
> ORDER BY fruits

Roland:

Ah ah, it works and it works well.

Thank you very much.

Now, if you are still there, could you please consider the following two
scenarios:

1.

I would also need another variant that would return:

-All rows common to both tables (subtracting the values of t2 from t1).

-All remaining rows from t2

-All remaining rows from t1

Is that possible? (with the sample tables this variant would return exactly
the same

results as your suggested SELECT but in my real life case the tables have
got

different values.)

2.

In my real life case, the field "quantity" is a numeric(14,2)

You CASTed to INTEGER so the decimal part got truncated.

I experimented with different options but could not make it to work.

I hope it is possible to CAST it to something else so as to have the decimal
part preserved.

Any suggestions on that?

Thank you very much

Carlos

Roland Wingerter

2006-12-04, 5:17 am

Carlos wrote

>
> Ah ah, it works and it works well.
>
> Thank you very much.
-------
You're welcome.

> 2.
>
> In my real life case, the field "quantity" is a numeric(14,2)
>
> You CASTed to INTEGER so the decimal part got truncated.
>
> I experimented with different options but could not make it to work.
-------
If "quantity" is numeric(14,2) there is no need to use CAST. This works for
me:

SELECT t1.fruits, t1.quantity-t2.quantity as quantity ;
FROM t1 ;
LEFT JOIN t2 ON (t1.fruits = t2.fruits) ;
WHERE (t1.quantity-t2.quantity) <> 0 ;
UNION ;
SELECT t2.fruits, t2.quantity ;
FROM t2 ;
WHERE t2.fruits not in (select t1.fruits from t1) ;
ORDER BY fruits

In my first solution the CAST was required because otherwise the result of
(t1.quantity-t2.quantity) was not compatible with the fieldtype of
t2.quantity. I guess casting to numeric(2,0) would have worked too.

> 1.
>
> I would also need another variant that would return:
>
> -All rows common to both tables (subtracting the values of t2 from t1).
------
Function two_1
SELECT t1.fruits, t1.quantity-t2.quantity ;
FROM t1 ;
JOIN t2 ON (t1.fruits = t2.fruits)

browse
return

In order to exclude records with quantity=0, add:
WHERE (t1.quantity-t2.quantity) <> 0

> -All remaining rows from t2
------
This can be solved by using the first SELECT statement in a subquery.

Function two_2

SELECT t2.fruits, t2.quantity ;
FROM t2 ;
WHERE t2.fruits not in ;
(SELECT t1.fruits FROM t1 ;
JOIN t2 as t ON (t1.fruits = t.fruits)) ;
ORDER BY fruits

browse
return

> -All remaining rows from t1
-------
Same as above, only switch t1 and t2.

Function two_3

SELECT t1.fruits, t1.quantity ;
FROM t1 ;
WHERE t1.fruits not in ;
(SELECT t2.fruits FROM t2 ;
JOIN t1 as t ON (t1.fruits = t.fruits)) ;
ORDER BY fruits

browse
return

> Is that possible? (with the sample tables this variant would return
> exactly
> the same
---------
The three statements above connected with UNION will hopefully give you the
desired result:

Function three
SELECT t1.fruits, t1.quantity-t2.quantity ;
FROM t1 ;
JOIN t2 ON (t1.fruits = t2.fruits) ;
UNION ;
SELECT t2.fruits, t2.quantity ;
FROM t2 ;
WHERE t2.fruits not in ;
(SELECT t1.fruits FROM t1 ;
JOIN t2 as t ON (t1.fruits = t.fruits)) ;
UNION ;
SELECT t1.fruits, t1.quantity ;
FROM t1 ;
WHERE t1.fruits not in ;
(SELECT t2.fruits FROM t2 ;
JOIN t1 as t ON (t1.fruits = t.fruits)) ;
ORDER BY fruits

browse
return

If you add the Where condition in query two_1
WHERE (t1.quantity-t2.quantity) <> 0
make sure to add it in all subselects as well.

Roland

2006-12-04, 7:14 pm

Short and sweet, exactly the way I like it.
It works like a charm.
You know, with these "unions" and "joins" going left and right,
I am lost left right, and everything in between (but with some
help I am getting there).
Thank you so much
No need to reply
Carlos

Roland Wingerter

2006-12-04, 7:14 pm

Carlos wrote

> Short and sweet, exactly the way I like it.
> It works like a charm.
------
Thanks for the success report.

Roland

Sponsored Links