Hi I have 3 SQL related Questions.

----------------------
1)I have two tables.

Table one contains

CellNo PP
1       2
2       1.5
3       0.95


Table 2 contains
CellNo Field Number1    P2
1      1       1       0.5
1      2       1       0.4
2      1       2       0.3
2      2       3       0.5
3      1       2       0.7



I need an sql statement that basically takes table 2, looks up which
CellNo's have a PP Value (in Table 1) of larger then 1, then goes and
takes the P2 value of these records in the resulting table and divides
P2 by the PP value of the Cell No. While the records who do not hove a
PP value larger then one are just taken from table2.

So The result should look like this.


CellNo Field Number1    P2
1      1       1       0.5/2=0.25
1      2       1       0.4/2=0.2
2      1       2       0.3/1.5=0.2
2      2       3       0.5/1.5=0.3333
3      1       2       0.7

Of course I just need the values in the P2 field (I added the
calculations so you see what calculations should be performed.). I am
looking for 1 sql statement. I this is not possible, then more then
one.

---------------------------

2) I have the following table and sql

Table1
Field1 Field2    Value
1        1        5
1        2        4
1        3        6
2        1        2
2        2        2
2        3        3
3        1        7
3        2        4
3        3        8

Table2
FieldOne
2
3


Then I have this sql:

Select FieldOne, Field2, min(value)
From table1, table2
Where (Field1=FieldOne)
Group by FieldOne

which produces this result (I guess)

FieldOne   Field2   minValue
   2         1         2
   3         2         4

My problem is that I only get one record that contains a minimum
value, altouth there are 2 records that contain the minimum value. Is
there a way that I get the 2 / 2 / 2 record to appear as well, because
it contains the same minValue and fulfills the criteria in the where
statement?

-------------------------------

3) How can I add the records of one table to the records of an other
table when the field names dont match?

For Example

I have

Field1   Field2 
1          2
2          3

and I have
FieldOne  FieldTwo
3          4
4          5

And I want
FieldOne  FieldTwo
1          2
2          3
3          4
4          5

---

Request for Question Clarification by answerguru-ga on 10 Aug 2003 19:40 PDT

Hi bnc_user-ga,

Didn't have time to get all three questions done, but here is the first one:

1. The following SQL query will achieve what you are looking for:

SELECT t1.CellNo, t2.Field, t2.Number1, (t2.P2/t1.PP) AS P2
FROM Table1 t1, Table2 t2
WHERE t1.CellNo = t2.CellNo
  AND t1.PP > 1

Hope that helps :)

answerguru-ga

---

Clarification of Question by bnc_user-ga on 11 Aug 2003 09:00 PDT

Thanks for your answer to question 1, I think your solution does not
bring the last record (that must not be divided) into the table (like
I wrote in my question ...While the records who do not have a PP Value
larger then one should just be taken from the table)

But thanks for trying.

I closed this question, because Jarlh-GA answered all three questions
in a comment. Too bad I cant give the money to him.

Thanks Jarlh-GA!!!

Hi bnc_user-ga,

For the question 3, the most simple solution is:

INSERT INTO Table1(Field1, Field2)
    SELECT FieldOne, FieldTwo FROM Table2;

This is true when you want all the records from Table2 to be added to
Table1. If you want only a range you can filter the records with a
WHILE clause in the SELECT.

Question 1:

Use CASE in the SELECT list:

SQL>select t2.cellno,field,number1,case when pp > 1 then p2/pp else p2
end
SQL&from t1,t2 where t1.cellno = t2.cellno;
     CELLNO       FIELD     NUMBER1
=========== =========== =========== =======================
          1           1           1   2.50000000000000E-001
          1           2           1   2.00000000000000E-001
          2           1           2   2.00000000000000E-001
          2           2           3   3.33333333333333E-001
          3           1           2   7.00000000000000E-001


If your database doesn't support CASE, you can use a UNION, like:

SQL>select t2.cellno,field,number1,p2/pp from t1,t2 where
SQL&t1.cellno = t2.cellno and pp > 1
SQL&union all
SQL&select t2.cellno,field,number1,p2 from t1,t2 where
SQL&t1.cellno = t2.cellno and pp <=1
SQL&order by 1;
     CELLNO       FIELD     NUMBER1
=========== =========== =========== =======================
          1           1           1  2.500000000000000E-001
          1           2           1  2.000000000000000E-001
          2           1           2  2.000000000000000E-001
          2           2           3  3.333333333333333E-001
          3           1           2  7.000000000000000E-001


Question 2:

SQL>select * from table1 where
SQL&field1 in (select * from table2) 
SQL&and val = (select min(val) from table1 as table1cop where
table1.field1 = table1cop.field1);

     FIELD1      FIELD2       VALUE
=========== =========== ===========
          2           1           2
          2           2           2
          3           2           4


Question 3:
insert into table1 select * from table2;


/Jarl