Sql Problem

[FoxKid]

I am having 2 tables name xyz and pqr
xyz is having fields pono,itemcode,itemname,orderqty
pqr is having fields slno,pono,itemname,receiveqty

now I am writing these codes to generate report :

sele xyz.*,pqr.receiveqty from xyz left outer join pqr;
on xyz.pono = pqr.pono where xyz.itemname = pqr.itemname;
into cursor abc

But in this the result is omiting all the records of xyz which is not having receiving in pqr.
I want to show all the records of xyz.

How is it possible...
Please help

Thanks and regards

 

[Mike Gagnon]

FoxKid

Take a look at Joins in the help file :

INNER JOIN specifies that the query result contains only rows from a table that match one or more rows in another table.

LEFT [OUTER] JOIN specifies that the query result contains all rows from the table to the left of the JOIN keyword and only matching rows from the table to the right of the JOIN keyword. The OUTER keyword is optional; it can be included to emphasize that an outer join is created.

RIGHT [OUTER] JOIN specifies that the query result contains all rows from the table to the right of the JOIN keyword and only matching rows from the table to the left of the JOIN keyword. The OUTER keyword is optional; it can be included to emphasize that an outer join is created.

FULL [OUTER] JOIN specifies that the query result contains all matching and non matching rows from both tables. The OUTER keyword is optional; it can be included to emphasize that an outer join is created.




Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[FoxKid]

Thanks Mike for your reply.
So according to this 'left join' can help me in solving my problem. But, when I am using it as :

sele xyz.*,pqr.receiveqty from xyz left join pqr;
on xyz.pono = pqr.pono where xyz.itemname = pqr.itemname;
into cursor abc

again, I am getting the same result and it is omitting records from xyz which has not havig any receiving

Thanks and Regards

 

[Mike Gagnon]

FoxKid

I would suggest you try FULL join "result contains all matching and non matching rows from both tables. "

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[FoxKid]

No mike its not working... I am writing here the actual codes I am using.

***********************temp.prg*****************

SET ENGINEBEHAVIOR 70
SELECT x.pono,x.podate,x.party,x.buyerorder,y.fabdesc,sum(y.order) as order,y.rate,y.targetdt;
from sukam!pomaster x INNER JOIN sukam!podetail y;
ON x.pono = y.pono;
WHERE x.cancel<>.t. and y.cancel<>.t.;
into CURSOR xporeport GROUP BY 1,5
*****(HERE ALL THE RECORDS ARE SELECTED WHICH HAS BEEN ORDERED)****************


***************FROM RECEIVING MASTER AND CHILD TABLE*****
SELECT a.grdate,b.pono,b.fabdesc,sum(b.receive) as receive,sum(b.reject) as reject;
from sukam!fabrecmas a LEFT JOIN sukam!fabrecdetail b;
ON a.grno = b.grno;
WHERE b.pono in (sele DISTINCT pono FROM xporeport);
into CURSOR xrecrep GROUP BY 1,2,3
*********************************************************


**********NOW ADDING BOTH ORDER AND RECEIVING***************
SELECT xporeport.*, xrecrep.grdate as recdt, xrecrep.receive as recqty, xrecrep.reject;
FROM xporeport FULL JOIN xrecrep;
ON xporeport.pono = xrecrep.pono;
WHERE XPOREPORT.FABDESC = XRECREP.FABDESC;
INTO CURSOR poreport readwrite
****(HERE SOME RECORDS ARE OMITTED FROM XPOREPORT)******


SELECT * from poreport INTO CURSOR detail ORDER BY party,podate,pono

report form .\reports\detail to print prompt preview

SET ENGINEBEHAVIOR 80

 

[ramani]

Hi

sele xyz.*,pqr.receiveqty from xyz ;
left outer join pqr;
on xyz.pono = pqr.pono ;
AND xyz.itemname = pqr.itemname;
into cursor abc



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/