## Message Posted: Tue, 07 Sep 2004 @ 18:14:14 GMT

 < Last>>

 Subj: SQL puzzle... From: Lerner, Dirk

Hi all,

I have found a sql problem which I can't explain myself.

I try to explain what I want to do:

I have two tables: bla1 (fact table) and bla2 (lookup table).

I want to join every row from bla1 with one row from bla2. The problem is, that I don't have allways a row (in bla2) for every row in bla1.

There are some (defined) rules to solve the problem: In bla2 for every combination (bdf,sai,ek_akt) there will be a genre 0. So if in bla1 genre is NULL then use 0. If genre is something else, but not in bla2.genre available then use 0 as genre.

I hope you understand what I mean. So, I made a sample as shown below:

```     create table bla1 (
bdf int
,sai int
,ek_akt int
,genre int)
;

create table bla2 (
bdf int
,sai int
,ek_akt int
,genre int)
;

insert into bla1 (1,109,3,);
insert into bla1 (1,109,3,0);
insert into bla1 (1,109,3,4);
insert into bla1 (1,109,3,6);

insert into bla2 (1,109,3,0);
insert into bla2 (1,109,3,4);

sel * from bla1;
sel * from bla2;
```

--The final select:

```     sel * from bla1 a
left join bla2 b
on (a.bdf=b.bdf
and a.sai=b.sai
and a.ek_akt=b.ek_akt
and a.genre=b.genre
)
or (a.bdf=b.bdf
and a.sai=b.sai
and a.ek_akt=b.ek_akt
and 0=b.genre)
;
```

My base tables:

```     bla1:
bdf     sai     ek_akt  genre
1       109     3       NULL
1       109     3       0
1       109     3       4
1       109     3       6

bla2:
bdf     sai     ek_akt  genre
1       109     3       0
1       109     3       4
```

normal left join:

```     bdf     sai     ek_akt  genre   bdf     sai     ek_akt  genre
1       109     3       NULL    NULL    NULL    NULL    NULL
1       109     3       0       1       109     3       0
1       109     3       4       1       109     3       4
1       109     3       6       NULL    NULL    NULL    NULL
```

all is fine and as expected.

Now with my rules:

```     bdf     sai     ek_akt  genre   bdf     sai     ek_akt  genre
1       109     3       NULL    1       109     3       0
1       109     3       0       1       109     3       0
1       109     3       4       1       109     3       0
1       109     3       4       1       109     3       4
1       109     3       6       1       109     3       0
```

Now I have the genre 4 two times. But why? Isn't the rule with an "OR" that if one part of the the OR-condition is true all is true and one row is selected? I expected the following:

```     bdf     sai     ek_akt  genre   bdf     sai     ek_akt  genre
1       109     3       NULL    1       109     3       0
1       109     3       0       1       109     3       0
1       109     3       4       1       109     3       4
1       109     3       6       1       109     3       0
```

Perhaps I'm too tired today to find my logical bug? :-)

Hope someone can help me :-)

Dirk

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback