display records from both tables: urgent help needed

[santosh1]

I got two tables, Table A & Table B

I want to put all items from Table A and Table B into the report. Both tables has
session ID field which will have same value, rest of the fields are different.
I did a EQUAL join on Table A with Table B to get all the records from Table A and from table B.
Then in the detail section of the report, inserted the fields from
both table. eg.
// fields from table A

itemID itemDescription Location Qty

// fields from table B

itemID itemDescription Location Qty


It is grouped by table1.ItemID and table2.itemID.
The result I get is: for each record in table A, all records from table B shows up.
Seems like this is not the right process.
Is there a simpler way to get all records from both tables without getting duplicate
records? Thanks a lot.

 

[synapsevampire]

Is the ID unique? What kind of join did you use?

You may find it simpler to use a Union and write your own SQL, but this is Crystal version dependent, and that's an unknown.

Copy the SQL (Database->Show SQL Query)

Type the word Union under it

paste back in your SQL

change the name of all references to the table to the other table.

From your example, the other fields aren't different, the values of the other fields are.

Try posting technical information, such as Crystal version, database used, connectivity used, sample data and expected output for optimal results.

-k

 

[santosh1]

I tried using UNION, gives me the same result. i.e.
for each record in Table A, shows all records from table B.
for example: table A has 2 records and table B has 3 records, it displays

tableA_item.1
tableB_item.1
tableB_item.2
tableB_item.3
tableA_item.2
tableB_item.1
tableB_item.2
tableB_item.3

Yes, the itemID is unique on both tables. The only similar field is SessionID where I do TableA LEFT join to Table B since sometimes Table B is empty, so I want all records from Table A and all records from Table B. I am using
crystal ver. 8.5. SQL Server 2000. Here's the actual SQL
that I used with UNION.

Table A is tsoPickListDetWrk
Table B is tsoPickListDetWrk_NMDP2

I want to display all records from both tables. I appreciate your help very much.

SELECT
tsoSOLineDist."OrigOrdered",
tsoPickListDetWrk."Comments", tsoPickListDetWrk."CustID", tsoPickListDetWrk."CustName", tsoPickListDetWrk."DefltShipDate", tsoPickListDetWrk."DefltShipMethID", tsoPickListDetWrk."DefltShipPriority", tsoPickListDetWrk."HeaderCmnt", tsoPickListDetWrk."ItemDescription", tsoPickListDetWrk."ItemID", tsoPickListDetWrk."KitShipLineKey", tsoPickListDetWrk."PickListNo", tsoPickListDetWrk."QtyToPick", tsoPickListDetWrk."ReqDelvDate", tsoPickListDetWrk."RcvgWhseID", tsoPickListDetWrk."RcvgWhseName", tsoPickListDetWrk."ShipDate", tsoPickListDetWrk."ShipMethID", tsoPickListDetWrk."ShipMethKey", tsoPickListDetWrk."ShipPriority", tsoPickListDetWrk."ShipToAddrCity", tsoPickListDetWrk."ShipToAddrCount", tsoPickListDetWrk."ShipToAddrLine1", tsoPickListDetWrk."ShipToAddrLine2", tsoPickListDetWrk."ShipToAddrLine3", tsoPickListDetWrk."ShipToAddrLine4", tsoPickListDetWrk."ShipToAddrLine5", tsoPickListDetWrk."ShipToAddrName", tsoPickListDetWrk."ShipToAddrPsCode", tsoPickListDetWrk."ShipToAddrStateID",
tsoPickListDetWrk."SOLineDistKey", tsoPickListDetWrk."TranNoRelChngOrd", tsoPickListDetWrk."TranType", tsoPickListDetWrk."UOM", tsoPickListDetWrk."WhseBinID",
timItem."UserFld1",
tarCustomer."UserFld1",
tsoPickListDetWrk_NMDP."PrevShippedQty"
FROM
{ oj (((("SES_app"."dbo"."tsoSOLineDist" tsoSOLineDist INNER JOIN "SES_app"."dbo"."tsoPickListDetWrk" tsoPickListDetWrk ON
tsoSOLineDist."SOLineDistKey" = tsoPickListDetWrk."SOLineDistKey&quot
INNER JOIN "SES_app"."dbo"."tarCustomer" tarCustomer ON
tsoPickListDetWrk."CustID" = tarCustomer."CustID&quot
INNER JOIN "SES_app"."dbo"."timItem" timItem ON
tsoPickListDetWrk."ItemID" = timItem."ItemID&quot
INNER JOIN "SES_app"."dbo"."tsoPickListDetWrk_NMDP" tsoPickListDetWrk_NMDP ON
tsoPickListDetWrk."SessionID" = tsoPickListDetWrk_NMDP."SessionID" AND
tsoPickListDetWrk."SOLineDistKey" = tsoPickListDetWrk_NMDP."SOLineDistKey&quot
LEFT OUTER JOIN "SES_app"."dbo"."tsoPickListDetWrk_NMDP2" tsoPickListDetWrk_NMDP2 ON
tsoPickListDetWrk."SessionID" = tsoPickListDetWrk_NMDP2."SessionID"}
ORDER BY
tsoPickListDetWrk."TranType" ASC,
tsoPickListDetWrk."TranNoRelChngOrd" ASC,
tsoPickListDetWrk."WhseBinID" ASC,
tsoPickListDetWrk."CustName" ASC,
tsoPickListDetWrk."ShipMethID" ASC,
tsoPickListDetWrk."ShipMethKey" ASC,
timItem."UserFld1" ASC,
tsoPickListDetWrk."ItemID" ASC

UNION

SELECT
tsoPickListDetWrk_NMDP2."itemDescription", tsoPickListDetWrk_NMDP2."QtyShipped", tsoPickListDetWrk_NMDP2."QtyOpentoShip", tsoPickListDetWrk_NMDP2."UOM", tsoPickListDetWrk_NMDP2."Location", tsoPickListDetWrk_NMDP2."itemID", tsoPickListDetWrk_NMDP2."PrevShippedQty"
FROM
{ oj (((("SES_app"."dbo"."tsoSOLineDist" tsoSOLineDist INNER JOIN "SES_app"."dbo"."tsoPickListDetWrk" tsoPickListDetWrk ON
tsoSOLineDist."SOLineDistKey" = tsoPickListDetWrk."SOLineDistKey&quot
INNER JOIN "SES_app"."dbo"."tarCustomer" tarCustomer ON
tsoPickListDetWrk."CustID" = tarCustomer."CustID&quot
INNER JOIN "SES_app"."dbo"."timItem" timItem ON
tsoPickListDetWrk."ItemID" = timItem."ItemID&quot
INNER JOIN "SES_app"."dbo"."tsoPickListDetWrk_NMDP" tsoPickListDetWrk_NMDP ON
tsoPickListDetWrk."SessionID" = tsoPickListDetWrk_NMDP."SessionID" AND
tsoPickListDetWrk."SOLineDistKey" = tsoPickListDetWrk_NMDP."SOLineDistKey&quot
LEFT OUTER JOIN "SES_app"."dbo"."tsoPickListDetWrk_NMDP2" tsoPickListDetWrk_NMDP2 ON
tsoPickListDetWrk."SessionID" = tsoPickListDetWrk_NMDP2."SessionID"}
ORDER BY
tsoPickListDetWrk_NMDP2."itemID" ASC

 

[synapsevampire]

Why are you grouping it?

You're getting all of the rows I think.

-k

 

[synapsevampire]

Sorry, I should have glanced at your SQL.

Your first post showed the same fields for both tables, the SQL shows entirely different ones, hence my earlier suggestion, but you want entirely different fields (although your post said that, your example did not).

Don't use a Union, join the tables and use a Left Outer, you should get everything from Table A, and the corresponding rows from Table B.

-k

 

[santosh1]

Now I can get records from both tables using LEFT outer join. The problem now is there are duplicates from table B for each record in Table A. For example:

tableA.itemID_1
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4

tableA.itemID_2

tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4

tableA.itemID_3
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4

How can I make items from table B display only once in the report?
Currently on the detail section A & B, I have fields from both tables arranged as below:

tableA.itemID tableA.item_Description tableA.Qty

tableB.itemID tableB.item_Description tableB.Qty

Could this be a problem? Please let me know how to fix this problem? Thanks a lot.

 

[santosh1]

I made a sub-report to display data from table B. The main report shows the report from table A. When the sub-report
is placed in the page footer, the report displays data from both table A & B which is what I wanted. I want sub-report to be displayed below main report. The page footer is way at the bottom and creates a lot of blank space when I put the sub-report at page footer. But, when I put the sub-report below the main report in the detail section, it repeats records from table B as follow. How can this be fixed? Thanks again.
For eg.

In main-report detail section

tableA.ItemID tableA.itemDescription tableA.Qty

sub-report_tableB


will displays:

tableA.itemID_1
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4
tableA.itemID_2
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4
tableA.itemID_3
tableB.itemID_1
tableB.itemID_2
tableB.itemID_3
tableB.itemID_4