Joining two tables problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Joining two tables problem

Author

Topic

new_bees
Starting Member

27 Posts

Posted - 2008-05-14 : 12:50:13

I really need help. I'm trying to create a product movement in a month's view.
Show all product that were sold but not (0) Received in month of April and show all product thas were Received but has not (0) Sold in April.

Here are the tables.

POP10500 Table - Records Product Received
Itemnmbr | QTYSHPPD | DATERECD
----------------------------------------
CABON-745 | 0 | 2/27/2008
AMINO1-989 | 50 | 4/2/2008
GINS-145 | 20 | 4/2/2008

SOP30300 Table - Records Product Sold
Itemnmbr | QTYORDER | ACTLSHIP
----------------------------------------
METSUL-989 | 20 | 4/10/2008
METSUL-989 | 10 | 4/9/2008
CABON-745 | 10 | 4/6/2008
CABON-745 | 15 | 4/5/2008
AMINO1-989 | |
GINS-145 | |

The Final Result should look like below:

Itemnmbr | Received | Sold
----------------------------------------
METSUL-989 | 0 | 30
CABON-745 | 0 | 25
AMINO1-989 | 50 | 0
GINS-145 | 20 | 0

As you can see in the tables above. The product "METSUL-989" in the SOP30300 table has not received shipment in the month of April therefore no record for that month in table POP10500, but has sold 30KG in the SOP30300 table.

When I use the Where clause "WHERE (((DATEPART([year], DATERECD) = '2008') AND (DATEPART([month], DATERECD) = '4')) OR DATERECD IS NULL)"
to query shipment received in April it's not counting the sold from the table SOP30300. The Final result should look like the above final result data.

How do I link these tables and still able to add the sold from SOP30300 even if that product has not order or no record in the POP10500 for the month of April?

MY SQL query:

SELECT MAX(a.ITEMNMBR) AS ITEMNMBR,
a.ITEMDESC AS Description,
SUM(b.Received) AS Received,
MAX(c.Sold) AS Sold
FROM IV00101 a
INNER JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) = @Year)
AND (DATEPART([month], DATERECD) = @Month)
GROUP BY ITEMNMBR
) b
ON a.ITEMNMBR = b.ITEMNMBR
INNER JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year)
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c ON a.ITEMNMBR = c.ITEMNMBR
GROUP BY a.ITEMDESC
ORDER BY a.ITEMDESC

Can you help me out again?

Thanks,

Newbee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 13:05:22

[code]SELECT MAX(a.ITEMNMBR) AS ITEMNMBR,
a.ITEMDESC AS Description,
ISNULL(SUM(b.Received),0) AS Received,
ISNULL(SUM(c.Sold),0) AS Sold
FROM IV00101 a
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) = @Year)
AND (DATEPART([month], DATERECD) = @Month)
GROUP BY ITEMNMBR
) b
ON a.ITEMNMBR = b.ITEMNMBR
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year)
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c ON a.ITEMNMBR = c.ITEMNMBR
GROUP BY a.ITEMDESC
ORDER BY a.ITEMDESC[/code]

new_bees
Starting Member

27 Posts

Posted - 2008-05-14 : 13:43:59

Visakh, thanks a lot, I think I'm getting closer. The code just need a little tweak.
I want to filter out the field marked in RED with no received and no sold in month. My boss don't want to see them.
Also be able to SUM up the received and sold by Description

ITEMNMBR | Description | Received | Sold
----------------------------------------------------------------------------------
1,3DIM110-001KGCTN | 1,3 Dimethylamlamine | .25 | .25
146AND110-001KGBAG | 1,4,6-andros | 0 | 0
ASCB-21 | Ascorbic C | 0 | 0
Amino25 | Amino Acid | 0 | 0
METISO110-005KGCTN | 5-Methyl-7-Methoxy | 0 | 0
METISO110-025KGDRM | 5-Methyl-7-Methoxy | 0 | 5
BETALA110-025KGCTN | Bete Alanine | 0 | 4
BETALA110-025KGDRM | Bete Alanine | 10 | 4

The Result should look like below table:
ITEMNMBR | Description | Received | Sold
----------------------------------------------------------------------------------
1,3DIM110-001KGCTN | 1,3 Dimethylamlamine | .25 | .25
METISO110-025KGDRM | 5-Methyl-7-Methoxy | 0 | 5
BETALA110-025KGCTN | Bete Alanine | 10 | 8

I tried to add a HAVING and where clause but no lock. It gives me an error.

SELECT MAX(a.ITEMNMBR) AS ITEMNMBR,
a.ITEMDESC AS Description,
ISNULL(SUM(b.Received),0) AS Received,
ISNULL(SUM(c.Sold),0) AS Sold
FROM IV00101 a
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) = @Year)
AND (DATEPART([month], DATERECD) = @Month)
GROUP BY ITEMNMBR
) b
ON a.ITEMNMBR = b.ITEMNMBR
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year)
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c ON a.ITEMNMBR = c.ITEMNMBR
WHERE (b.Received <> 0) AND (c.Sold = 0)
GROUP BY a.ITEMDESC
HAVING ISNULL(SUM(c.Sold))
ORDER BY a.ITEMDESC

Thanks a lot...

New Bee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 13:55:14

quote:
Originally posted by new_bees

Visakh, thanks a lot, I think I'm getting closer. The code just need a little tweak.
I want to filter out the field marked in RED with no received and no sold in month. My boss don't want to see them.
Also be able to SUM up the received and sold by Description

ITEMNMBR | Description | Received | Sold
----------------------------------------------------------------------------------
1,3DIM110-001KGCTN | 1,3 Dimethylamlamine | .25 | .25
146AND110-001KGBAG | 1,4,6-andros | 0 | 0
ASCB-21 | Ascorbic C | 0 | 0
Amino25 | Amino Acid | 0 | 0
METISO110-005KGCTN | 5-Methyl-7-Methoxy | 0 | 0
METISO110-025KGDRM | 5-Methyl-7-Methoxy | 0 | 5
BETALA110-025KGCTN | Bete Alanine | 0 | 4
BETALA110-025KGDRM | Bete Alanine | 10 | 4

The Result should look like below table:
ITEMNMBR | Description | Received | Sold
----------------------------------------------------------------------------------
1,3DIM110-001KGCTN | 1,3 Dimethylamlamine | .25 | .25
METISO110-025KGDRM | 5-Methyl-7-Methoxy | 0 | 5
BETALA110-025KGCTN | Bete Alanine | 10 | 8

I tried to add a HAVING and where clause but no lock. It gives me an error.

SELECT MAX(a.ITEMNMBR) AS ITEMNMBR,
a.ITEMDESC AS Description,
ISNULL(SUM(b.Received),0) AS Received,
ISNULL(SUM(c.Sold),0) AS Sold
FROM IV00101 a
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) = @Year)
AND (DATEPART([month], DATERECD) = @Month)
GROUP BY ITEMNMBR
) b
ON a.ITEMNMBR = b.ITEMNMBR
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year)
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c ON a.ITEMNMBR = c.ITEMNMBR
WHERE (b.Received <> 0) AND (c.Sold = 0)
GROUP BY a.ITEMDESC
HAVING ISNULL(SUM(c.Sold))
ORDER BY a.ITEMDESC

Thanks a lot...

New Bee

SELECT MAX(a.ITEMNMBR) AS ITEMNMBR, 
a.ITEMDESC AS Description, 
ISNULL(SUM(b.Received),0) AS Received, 
ISNULL(SUM(c.Sold),0) AS Sold
FROM IV00101 a 
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) = @Year) 
AND (DATEPART([month], DATERECD) = @Month) 
GROUP BY ITEMNMBR
) b 
ON a.ITEMNMBR = b.ITEMNMBR 
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year) 
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c ON a.ITEMNMBR = c.ITEMNMBR
GROUP BY a.ITEMDESC
HAVING (SUM(b.Received) + SUM(c.Sold)) >0
ORDER BY a.ITEMDESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources