Sql Query From Db2

annamaria · #1 (**permalink**) 04-20-07, 15:18

I Would Like To Join Two Tables:

Select X, Y
From Table1

Select A, Z
From Table2

They Don't Have Any Field In Common, And I Want To Join
Field Y = Field Z (when They Have The Same Value The Two
Records Must Be Joined In A Single Record).

Thank You

Anna - Verona (italy)

Marcus_A · #2 (**permalink**) 04-20-07, 16:41

select X, Y, A, Z
from Table1, Table2
where Y = Z

This may not work if Y and Z have different data types.

annamaria · #3 (**permalink**) 04-22-07, 06:06

Excuse me but I didn't explain the matter correctly (the answer is in any
case useful for future queries).
What I really want to do is this:
I want to select all the records from table1 and if one of its fields (field3) has the same value of fieldA in table2 I want to match the two records:
For example:
Table1
field1 field2 field3
sec.1 500 USD
sec.2 300 EUR
sec.3 400 GBP
Table2
fieldA fieldB
USD 1,36
GBP 0.678
YEN 161,593
RESULT:
field1 field2 field3 fieldA fieldB
sec.1 500 USD USD 1,36
sec.2 300 EUR
sec.3 400 GBP GBP 0.678

Thank you for your attention.

Anna - Verona (Italy)

Peter.Vanroose · #4 (**permalink**) 04-22-07, 06:25

Quote:

Originally Posted by annamaria

I want to select all the records from Table1

That will have to be a (left) outer join:

Code:

SELECT field1, field2, field3, fieldA, fieldB
FROM   Table1 LEFT OUTER JOIN Table2
       ON field3 = fieldA

It will indeed leave the entries fieldA and fieldB empty (NULL) when there is no match, but still show the Table1 rows:

Code:

field1 field2 field3 fieldA fieldB
sec.1     500 USD    USD     1.36
sec.2     300 EUR    ------ ------
sec.3     400 GBP    GBP     0.678

This means that you could even filter on the fieldA entries being NULL, i.e., not present in Table2, even if fieldA is a primary key of Table2 and hence never NULL in Table2:

Code:

SELECT field1, field2, field3
FROM   Table1 LEFT OUTER JOIN Table2
       ON field3 = fieldA
WHERE  fieldA IS NULL

would return

Code:

field1 field2 field3
sec.2     300 EUR

which is exactly the same result as (but possibly more performant than) from the query

Code:

SELECT field1, field2, field3
FROM   Table1
WHERE  field3 NOT IN (SELECT fieldA FROM Table2)

annamaria · #5 (**permalink**) 04-22-07, 06:53

Thank you so much, you are solving all my problems this morning!

I want to ask you, with reference to the other thread I posted, you answered me to use the command COALESCE. Is it a command that can be used in SQL for DB2 queries? Or is it for SQLServer? And what about LEFT
OUTER JOIN? I'm at home at the moment and I don't have QMf here so I
can't try the commands.

Thank you again.

Anna - Verona (Italy)

Peter.Vanroose · #6 (**permalink**) 04-22-07, 06:58

Quote:

Originally Posted by annamaria

... the command COALESCE: is it a command that can be used in SQL for DB2 queries? And what about LEFT OUTER JOIN?

Both are standerd SQL and can (nowadays) be used in any RDBMS, including DB2, SQLServer and Oracle.

annamaria · #7 (**permalink**) 04-22-07, 07:45

Please help me again.

This is a big query I'm making and problems come up keeping on thinking
about it.

I must select from three tables.
The first is table1 we talked about in the previous post. I want always to select ALL THE RECORDS from this table.
I want to select some fields from table2 which doesn't have values for all
the records of table1 (like the previous case). I want to select field2.1 and field2.2 from table2 only if (field2.1 < chosen date and field2.2 > chosen date). Where shall I write this selection?;
Now I have all records of table1 with some records bearing data of table2.
Then I have table3 which is table2 of the previous post. I want to outer join this table .
For example:
Table1
field1 field2 field3
sec.1 500 USD
sec.2 300 EUR
sec.3 400 GBP
Table2
field1a field 2a field 2b
sec.2 20070210 20070410
sec.3 20070415 20071015
Table3
fieldA fieldB
USD 1,36
GBP 0.678
YEN 161,593
DESIRED RESULT:
field1 field2 field3 field2a field2b fieldBA
sec.1 500 USD 1,36
sec.2 300 EUR 20070210 20070410
sec.3 400 GBP 20070415 20071015 0.678

How will I write the 'from' clause?
select from table1 left outer join table2 left outer join table 3. Is it okay?
Or maybe instead of the first left outer join I could write:
select
field1, ......,
case when (field2a < chosen date and field 2b > chosen date) as field2a and field2b (?????)
from table1, table2 left outer join table3 (excuse me if I dare...)

Another question: are there problems with left outer joins if I have nested tables inside the query?

Thank you. I promise this is my last post for today.

Anna - Italy (Verona)

Peter.Vanroose · #8 (**permalink**) 04-22-07, 15:17

Quote:

Originally Posted by annamaria

DESIRED RESULT:
field1 field2 field3 field2a field2b fieldBA
sec.1 500 USD 1,36
sec.2 300 EUR 20070210 20070410
sec.3 400 GBP 20070415 20071015 0.678

select from table1 left outer join table2 left outer join table 3. Is it okay?

That's right: if no rows of table1 should be filtered away, you must LEFT OUTER JOIN it with all subsequent tables.

Now for the join condition:

If you would filter after joining, some rows of table1 could disappear, so there should not be any WHERE conditions, at least not in the outer query.

Instead, first reduce table2 to its minimal proportions, filtering away any unnecessary rows and/or columns:

Code:

SELECT field2a, field2b
FROM   table2
WHERE  <some condition>

Now, outer join table1 to this reduced table:

Code:

SELECT field1, field2, field3, field2a, field2b, fieldB
FROM   table1
       LEFT OUTER JOIN
       (SELECT field1a, field2a, field2b
        FROM   table2
        WHERE  <some condition>) AS t2
       ON table1.field1 = t2.field1a
       LEFT OUTER JOIN table3
       ON field3 = fieldA

As you can see, I had to add field1a to the t2 definition in order to be able to join it to table1.

(Not that, with DB2 version 8, you could equivalently use a common table expression for t2; this would improve readability.)

annamaria · #9 (**permalink**) 04-22-07, 16:22

Quote:

Originally Posted by Peter.Vanroose

(Not that, with DB2 version 8, you could equivalently use a common table expression for t2; this would improve readability.)

.
What do you mean by 'common table expression'?

Don't you know if nested tables (i.e. select from (select from....) cause problems to outer joins?

Bye

Anna - Verona (Italy)

Peter.Vanroose · #10 (**permalink**) 04-22-07, 17:17

Quote:

Originally Posted by annamaria

What do you mean by 'common table expression'?

Here is the CTE version of my previous query:

Code:

WITH t2 AS
(SELECT field1a, field2a, field2b
 FROM   table2
 WHERE  <some condition>) 
SELECT field1, field2, field3, field2a, field2b, fieldB
FROM   table1  LEFT OUTER JOIN  t2
       ON field1 = field1a
       LEFT OUTER JOIN table3
       ON field3 = fieldA

So it's a syntactic alternative for a nested table expression.

Quote:

Originally Posted by annamaria

Do you know if nested tables (i.e. select from (select from....) cause problems to outer joins?

No, they don't.