select in 3 Tables...

Main Page | Report this Page

Computers Forum Index » Computer - Databases - Informix » select in 3 Tables...

Page 1 of 1

select in 3 Tables...

Author

Message

Ralf Hackmann...

Posted: Tue Oct 27, 2009 2:47 pm

Guest

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...

Posted: Tue Oct 27, 2009 6:53 pm

Guest

Ralf Hackmann wrote:

Quote:

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...

Posted: Tue Oct 27, 2009 7:06 pm

Guest

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

Quote:

From: ralf.hackmann at (no spam) gmail.com
Subject: select in 3 Tables
Date: Tue, 27 Oct 2009 07:47:47 -0700
To: informix-list at (no spam) iiug.org

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

_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

_________________________________________________________________
Windows 7: I wanted more reliable, now it's more reliable. Wow!
http://microsoft.com/windows/windows-7/default-ga.aspx?h=myidea?ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_myidea:102009

Art Kagel...

Posted: Wed Oct 28, 2009 1:30 am

Guest

VERSION?

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art at (no spam) 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.

On Tue, Oct 27, 2009 at 10:47 AM, Ralf Hackmann <ralf.hackmann at (no spam) gmail.com>wrote:

Quote:

Ralf Hackmann...

Posted: Wed Oct 28, 2009 10:42 am

Guest

On 27 Okt., 22:30, Art Kagel <art.ka... at (no spam) gmail.com> wrote:

Quote:

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...

Posted: Wed Oct 28, 2009 7:36 pm

Guest

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 (art at (no spam) 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.

On Wed, Oct 28, 2009 at 6:42 AM, Ralf Hackmann <ralf.hackmann at (no spam) gmail.com>wrote:

Quote:

On 27 Okt., 22:30, Art Kagel <art.ka... at (no spam) 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;

_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Jonathan Leffler...

Posted: Mon Nov 02, 2009 6:15 am

Guest

On Wed, Oct 28, 2009 at 07:36, Art Kagel <art.kagel at (no spam) gmail.com> wrote:

Quote:

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.

Quote:

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.

On Wed, Oct 28, 2009 at 6:42 AM, Ralf Hackmann <ralf.hackmann at (no spam) gmail.com>wrote:

On 27 Okt., 22:30, Art Kagel <art.ka... at (no spam) 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;

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler at (no spam) earthlink.net, jleffler at (no spam) 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<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>
- "May you live every day of your life."

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler at (no spam) earthlink.net, jleffler at (no spam) 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<http://www.brainyquote.com/quotes/authors/s/samuel_goldwyn.html>
- "I'm willing to admit that I may not always be right, but I am never
wrong."

Page 1 of 1

All times are GMT The time now is Thu Dec 10, 2009 4:04 am Computers Forum Index