SQL Joins and Cost

[Karsten]

Hi everybody!

I need to compose a SQL statement for the following task. I have three
tables

T1: refkey, value
T2: refkey, indexnumber, indexvalue
T3: refkey, functionnumber

There are several entries for refkey in each tables, it is not unique.
Refkey is the link for all three tables. I need to select all t1.value
for which the following conditions are true:
(t1.refkey=t2.r efkey=t3.refkey )

C1. If t2.indexnumber= 100 and t2.indexvalue=0 , and there is no
t2.indexnumber= 105 for this refkey, then select

C1. If t2.indexnumber= 100 and t2.indexvalue=0 , and there is
t2.indexnumber= 105 for this refkey and its t2.indexvalue=0 , then
select

C3. If t3.functionnumb er is not in (200,300) and C1 or C2 is true,
then select

I'm completely unable to write this sql statement. Any advise on how
to join these tables. BTW: We are talking about 12mio entries in T1
and the select needs to be cost sensitive.

Thanks alot for your help
Karsten

[Knut Stolze]

Re: SQL Joins and Cost

Karsten <k_krieg@web.de > wrote:
[color=blue]
> Hi everybody!
>
> I need to compose a SQL statement for the following task. I have three
> tables
>
> T1: refkey, value
> T2: refkey, indexnumber, indexvalue
> T3: refkey, functionnumber
>
> There are several entries for refkey in each tables, it is not unique.
> Refkey is the link for all three tables. I need to select all t1.value
> for which the following conditions are true:
> (t1.refkey=t2.r efkey=t3.refkey )
>
> C1. If t2.indexnumber= 100 and t2.indexvalue=0 , and there is no
> t2.indexnumber= 105 for this refkey, then select
>
> C1. If t2.indexnumber= 100 and t2.indexvalue=0 , and there is
> t2.indexnumber= 105 for this refkey and its t2.indexvalue=0 , then
> select
>
> C3. If t3.functionnumb er is not in (200,300) and C1 or C2 is true,
> then select[/color]

How would you combine all three conditions? C1 AND C2 AND C3 or (C1 OR C2)
AND C3 or something else? For AND, you could use this query:

SELECT t1.value
FROM t1
WHERE
-- this is for condition C1
EXISTS ( SELECT *
FROM t2
WHERE t2.refkey = t1.refkey AND
t2.indexnumber = 100 AND
t2.indexvalue = 0 AND
NOT EXISTS ( SELECT *
FROM t2
WHERE t2.indexnumber = 105 AND
t2.refkey = t1.refkey ) ) AND
-- this is for condition C2
EXISTS ( SELECT *
FROM t2
WHERE t2.refkey = t1.refkey AND
t2.indexnumber = 100 AND
t2.indexvalue = 0 AND
NOT EXISTS ( SELECT *
FROM t2
WHERE t2.indexnumber = 105 AND
t2.indexvalue = 0 ) ) AND
-- condition C3
EXISTS ( SELECT *
FROM t3
WHERE t3.functionnumb er NOT IN ( 200, 300 ) AND
t2.refkey = t1.refkey )


If you need to combine the conditions in a different way, you could just use
the building blocks and combine them appropriately.
[color=blue]
> I'm completely unable to write this sql statement. Any advise on how
> to join these tables. BTW: We are talking about 12mio entries in T1
> and the select needs to be cost sensitive.[/color]

First you get your query to work, then you can look into optizing the query
or the physical table design. Everything else is just pointless.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena