select in 3 Tables

[Ralf Hackmann]

The table fyrkr1 has 1052 records

select krd.knr, krd.nam, krd.tlf, krd.liefnr, krd.zahlbed,
zlb.zahlbed, krd.liefbed, lfb.liefbed, zlb.sa, lfb.sa, zlb.bezeich1,
lfb.bezeich
from fyrkr1 krd, fyzlb zlb , fylfb lfb
where (krd.zahlbed=zlb.zahlbed)
and (krd.liefbed=lfb.liefbed)
and lfb.sa = 1
and zlb.sa = 1;

After the select statement above I get only 559 results.
I think the reason is that some records of the fields fyrkr1.zahlbed
and fyrkr1.liefbed have no entriess.

I tried

select krd.knr, krd.nam, krd.tlf, krd.liefnr, krd.zahlbed,
zlb.zahlbed, krd.liefbed, lfb.liefbed, zlb.sa, lfb.sa, zlb.bezeich1,
lfb.bezeich
from fyrkr1 krd left join fyzlb zlb left join fylfb lfb
where (krd.zahlbed=zlb.zahlbed)
and (krd.liefbed=lfb.liefbed)
and lfb.sa = 1
and zlb.sa = 1;

But I get an syntax errot message

What mus I do to get the whole list with the 1052 record?

Thanks

Ralf

[Obnoxio The Clown]

Outer join?

select krd.knr, krd.nam, krd.tlf, krd.liefnr, krd.zahlbed,
zlb.zahlbed, krd.liefbed, lfb.liefbed, zlb.sa, lfb.sa, zlb.bezeich1,
lfb.bezeich

from fyrkr1 krd, outer fyzlb zlb, outer fylfb lfb

where (krd.zahlbed=zlb.zahlbed)
and (krd.liefbed=lfb.liefbed)
and lfb.sa = 1
and zlb.sa = 1;

Perhaps?
--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

[Ian Michael Gumby]

Ugh!

Look, if you're going to do this, please make the SQL query readable.
Taking your initial query... Assuming that this doesn't get messed up in formatting...

SELECT A.knr, A.nam, A.tlf, A.liefnr, A.zahlbed,
           B.zahlbed, A.liefbed, C.liefbed, B.sa,
           C.sa, B.bezeich1, C.bezeich
FROM    fyrkr1 A, fyzlb B , fylfb C
WHERE  A.zahlbed = B.zahlbed
AND      A.liefbed = C.liefbed
AND      C.sa = 1
AND      B.sa = 1;

A little bit easier to read, no?
Now I notice that you have a couple of repeated columns, so lets remove those...

SELECT A.knr, A.nam, A.tlf, A.liefnr, A.zahlbed,
           A.liefbed, B.sa, C.sa, B.bezeich1, C.bezeich
FROM    fyrkr1 A, fyzlb B , fylfb C
WHERE  A.zahlbed = B.zahlbed
AND      A.liefbed = C.liefbed
AND      C.sa = 1
AND      B.sa = 1;

Note: we can remove those columns because we know that they will contain the same value as the column in table A.

Now your question is that you have 1052 rows of data, however, you only see 559 records when you run this query.
Yes, that's possible because in the query, unless you have a row present in the join, you will not see the row in A.

You need to do an OUTER JOIN between table A and table B and table C.
That is to say, you want the row in table A, and also the data in table B or C if the data exists.
This way you'll get all the rows in A, and if there's a matching row in B and/or a matching row in C you'll get that data too.

Now the reason I rewrote the query is that I'm an old man, slightly dyslexic so that its easier to read a well formatted query.
Note the INFORMIX KEY WORDS ARE IN CAPS. The alias is also a capital letter and a single letter.

HTH

-G

> _______________________________________________
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

---

Windows 7: I wanted more reliable, now it's more reliable. Wow!

[Art Kagel]

VERSION?

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

[Ralf Hackmann]

On 27 Okt., 22:30, Art Kagel <art.ka...@gmail.com> wrote:
> VERSION?
>
dbaccess -v
DB-Access Version 7.25.UC6R1
Software Serial Number AAA#B000000

isql -v
IBM INFORMIX-SQL Version 7.32.UC4
Software Serial Number RDS#N000000

But I checked my syntax and saw that I forgot a comma "," before
"OUTER"
Now it runs

I thougt there is a comma-Join "," , a OUTER JOIN, INNER JOIN, RIGHT
JOIN, LEFT JOIN,
and if I use OUTER JOIN, INNER JOIN, RIGHT JOIN, LEFT JOIN, I dont
have to set ","
befor it.
I used this tutorial
http://www.sql-und-xml.de/sql-tutorial/tabellen-verknuepfen-mit-join.html

Hier is the right query that shows alle 1052 record

Thanks all

UNLOAD TO lieferantenstamm.csv DELIMITER ";"
SELECT krd.knr, krd.nam, krd.tlf, krd.liefnr, zlb.bezeich1,
lfb.bezeich
FROM fyrkr1 krd, OUTER fyzlb zlb, OUTER fylfb lfb
WHERE (krd.zahlbed=zlb.zahlbed)
AND (krd.liefbed=lfb.liefbed)
AND lfb.sa = 1
AND zlb.sa = 1;

[Art Kagel]

Neither dbaccess 7.25 nor isql 7.32 support the ANSI '92 syntax for outer joins, you have to use the older syntax that Informix has always supported for outer joins.  That means that the INNER, LEFT, RIGHT, and FULL keywords are not recognized and OUTER requires a comma separator and is only  left outer join.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

[Jonathan Leffler]

On Wed, Oct 28, 2009 at 07:36, Art Kagel <art....@gmail.com> wrote:

Neither dbaccess 7.25 nor isql 7.32 support the ANSI '92 syntax for outer joins,

It is not something that DB-Access or ISQL has to recognize; it is all down to whether the underlying server recognizes the Standard SQL (it is an ISO standard more than an ANSI standard) notation.

The programs cited just know how to find a semi-colon that isn't inside a comment or string and send the material that precedes it to the database for interpretation.  In the case of ISQL, it is pretty much literally that simple - so it doesn't (yet) handle CREATE PROCEDURE statements very well.  In the case of DB-Access, it does know how to handle the syntax of a CREATE PROCEDURE statement.

But none of that matters for a single SELECT statement using OUTER notations.

That said, DB-Access 7.25 comes with SE 7.25, and SE does not understand Standard SQL outer joins, so the old-style Informix OUTER join notation is all that you can use.

ISQL 7.32 can connect to SE 7.2x - but since the server does not recognize Standard SQL outer joins, it is no help.  ISQL 7.32 can also connect to IDS 7.3x and later - and the supported products (10.00 and later) do handle Standard SQL outer join notations.

 

you have to use the older syntax that Informix has always supported for outer joins.  That means that the INNER, LEFT, RIGHT, and FULL keywords are not recognized and OUTER requires a comma separator and is only  left outer join.

--
Jonathan Leffler                   #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Jonathan Swift  - "May you live every day of your life."

--
Jonathan Leffler                   #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Samuel Goldwyn  - "I'm willing to admit that I may not always be right, but I am never wrong."