Hallo Folks,

first, let me say im sorry for my bad english.

I've got a Firebird Database with 3 Tables:

ARTIKEL
ARTIKEL_EAN
ARTIKEL_RETURNABLE_BOTTLE

First ARTIKEL:
with the columns
ARTNR ARTNA ARTPR
===== ===== =====
1 Apple 0,50
2 Beer 0,99
3 Fish 2,99
...

ARTIKEL_EAN
with the columns
ARTNR EAN
===== ====
1 401235465
2 132456789
3 400567890
....

ARTIKEL_RETURNABLE_BOTTLE
with the columns
ARTNR RETURNABLE
===== ====
2 468

I'm using a shellscript on Suse to execute my query and save it to an *.csv-File. Here is my Script:
Now i get the following result:

ARTNR ARTNA EAN ARTPR RETURNABLE
<null>
2 Beer 132456789 0,99 468
<null>

But it should be:
ARTNR ARTNA EAN ARTPR RETURNABLE
1 Apple 401235465 0,50 <null>
2 Beer 132456789 0,99 468
3 Fish 400567890 2,99 <null>

So only if there a returnable bottle i get an result. Where's my mistake?

Thank you for your help

andras

Please remove all non-SQL stuff from your posting and use [code] tags to make it readable

Due to that "posting mess" it's pretty hard to identify and understand the actual SQL you are using, but at first glance it seems to me that the outer join that you are using is causing this behaviour. (LEFT JOIN is the same as LEFT OUTER JOIN).

I would expect that if you change LEFT JOIN to JOIN you will not get the rows with NULLs.

Hello Shammat,

thank you for your answer, i get used to use the code tags.
Sorry for the long, hard readable post.

In my first post was a mistake. the ARTIKEL_RETURNABLE_BOTTLE table dont have a row for every article, only for the articles which are returnable.

If i use join instant of left join i only get the articles which are returnable. With left join i get all articles, but the articles who are not returnable are only shown as <null>.

thats what i tried to say with:
ARTNR ARTNA EAN ARTPR RETURNABLE
<null>
2 Beer 132456789 0,99 468
<null>

I don't understand you.
I thought you want to remove "not returnable" items?

thank you again for be so kindly with me.

My problem is, that i want a csv file like this:
1;Apple;401235465;0,50;<null>
2;Beer;132456789;0,99;468
3;Fish;400567890;2,99;<null>

But all i get is this:
<null>
2;Beer;132456789;0,99;468
<null>

Is there a way to do change the "<null>" to an "0" (maybe case or if)? I think that the <null> is destroying the row. So that i get
1;Apple;401235465;0,50;0
2;Beer;132456789;0,99;468
3;Fish;400567890;2,99;0

I hope that this is more understandable.

i found my mistake!

when my CODE is like this
i get
<null>
2;Beer;132456789;0,99;468
<null>

when my code is like this
i get
1 Apple 401235465 0,50 <null>
2 Beer 132456789 0,99 468
3 Fish 400567890 2,99 <null>

But i need to get an csv file where the data is comma seperated. Do anyone know how i can get this file the way i'm looking forward to?