select in 3 Tables
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
Ralf Hackmann wrote:
> 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?
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.
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
> From: ralf.hackmann@gmail.com
> Subject: select in 3 Tables
> Date: Tue, 27 Oct 2009 07:47:47 -0700
> To: informix-list@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@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
VERSION?
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@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@gmail.com>wrote:
> 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@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>