JOIN only selects first record

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 2008 Forums

Transact-SQL (2008)

JOIN only selects first record

Author

Topic

runnerpaul
Starting Member

24 Posts

Posted - 2013-09-04 : 07:54:37

Hi,

I wish to change my query(see below) so that when the CUSTPACKINGSLIPTRANS table is joined, only the first record for each PACKINGSLIPID is selected. Is this possible? A unique identifying field in this table is RECID.

Cheers
Paul

SELECT    TOP (100) PERCENT 
dbo.CUSTPACKINGSLIPTRANS.DIMENSION2_ [Load Type], 
dbo.PURCHLINE.DIMENSION3_ AS [Cost Centre], CASE WHEN Month(dbo.PURCHLINE.DELIVERYDATE) 
                      <= 2 THEN year(dbo.PURCHLINE.DELIVERYDATE) - 1 WHEN Month(dbo.PURCHLINE.DELIVERYDATE) >= 4 THEN year(dbo.PURCHLINE.DELIVERYDATE) 
                      WHEN Month(dbo.PURCHLINE.DELIVERYDATE) = 3 AND year(dbo.PURCHLINE.DELIVERYDATE) <= 2011 THEN year(dbo.PURCHLINE.DELIVERYDATE) 
                      ELSE year(dbo.PURCHLINE.DELIVERYDATE) - 1 END AS [Fin Year], CASE WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2012 AND 
                      MONTH(dbo.PURCHLINE.DELIVERYDATE) <= 2 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 10 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2011 AND 
                      MONTH(dbo.PURCHLINE.DELIVERYDATE) >= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 2 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) = 2012 AND 
                      MONTH(dbo.PURCHLINE.DELIVERYDATE) = 3 THEN 13 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2013 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) 
                      <= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 9 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) 
                      >= 4 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 3 END AS Period, 
                      CASE dbo.PURCHTABLE.PURCHASETYPE WHEN '0' THEN 'Journal' WHEN '1' THEN 'Quotation' WHEN '2' THEN 'Subscription' WHEN '3' THEN 'Purchase Order' WHEN
                       '4' THEN 'Returned Item' WHEN '5' THEN 'Blanket Order' END AS [Order Type], dbo.PURCHTABLE.PURCHID AS [Purchase Order], 
                      dbo.PURCHTABLE.PROJID AS Project, CONVERT(varchar(12), dbo.PURCHTABLE.CREATEDDATE, 111) AS [Trans Date], 
                      dbo.PURCHTABLE.VENDORREF AS [Vendor Requistion], 
                      CASE dbo.PURCHTABLE.PURCHSTATUS WHEN '0' THEN 'None' WHEN '1' THEN 'Open Order' WHEN '2' THEN 'Received' WHEN '3' THEN 'Invoiced' WHEN '4' THEN 'Canceled'
                       END AS [Order Status], dbo.PURCHTABLE.CURRENCYCODE AS [Order Currency], dbo.PURCHTABLE.ORDERACCOUNT AS [Vendor Account], 
                      dbo.PURCHTABLE.INVOICEACCOUNT AS [Invoice Account], dbo.VENDTABLE.NAME AS [Vendor Name], DIMENSIONS_1.DESCRIPTION AS [Product Group], 
                      dbo.INVENTTABLE.ITEMGROUPID AS [Item Group], dbo.INVENTTABLE.SECTION, dbo.PURCHLINE.ITEMID AS [Stock Code], 
                      dbo.INVENTTABLE.ITEMNAME AS [Stock Desc], dbo.PURCHLINE.PURCHUNIT AS [Unit of Purchase], dbo.PURCHLINE.PURCHQTY AS [Qty Ordered], 
                      dbo.PURCHLINE.REMAINPURCHPHYSICAL AS [Qty Remaining], dbo.INVENTTABLE.NETWEIGHT AS [Item Weight], dbo.PURCHLINE.PURCHPRICE AS [Unit Price], 
                      dbo.PURCHLINE.LINEPERCENT AS [Dicount %], dbo.PURCHLINE.LINEDISC AS [Discount Amt], dbo.PURCHLINE.LINEAMOUNT AS [Net Amount(Pos)], 
                      CASE dbo.PURCHLINE.BLOCKED WHEN '0' THEN 'No' WHEN '1' THEN 'Yes' END AS [Order Line Stopped?], dbo.PURCHLINE.DIMENSION AS [Site/Location], 
                      dbo.PURCHLINE.DIMENSION4_ AS Department, dbo.PURCHLINE.DIMENSION6_ AS [Job Number], dbo.INVENTDIM.INVENTLOCATIONID AS Warehouse, 
                      dbo.EMPLTABLE.NAME AS [Sales Person], CONVERT(varchar(12), dbo.PURCHLINE.DELIVERYDATE, 111) AS [Delivery Date], 
                      dbo.INVENTPOSTING.LEDGERACCOUNTID AS [GL Code], dbo.LEDGERTABLE.ACCOUNTNAME AS [GL Name], 
                      dbo.INVENTTABLEMODULE.PRICE AS [Standard Cost Price], dbo.PROJVISITLOADS.LOADWEIGHT AS [Load Weight]
FROM         dbo.VENDTABLE INNER JOIN
                      dbo.PURCHLINE INNER JOIN
                      dbo.INVENTTABLE ON dbo.PURCHLINE.ITEMID = dbo.INVENTTABLE.ITEMID INNER JOIN
                      dbo.PURCHTABLE ON dbo.PURCHLINE.PURCHID = dbo.PURCHTABLE.PURCHID AND dbo.PURCHLINE.DATAAREAID = dbo.PURCHTABLE.DATAAREAID ON 
                      dbo.VENDTABLE.ACCOUNTNUM = dbo.PURCHTABLE.ORDERACCOUNT INNER JOIN
                      dbo.INVENTPOSTING ON dbo.INVENTTABLE.ITEMGROUPID = dbo.INVENTPOSTING.ITEMRELATION INNER JOIN
                      dbo.LEDGERTABLE ON dbo.INVENTPOSTING.LEDGERACCOUNTID = dbo.LEDGERTABLE.ACCOUNTNUM INNER JOIN
                      dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID LEFT OUTER JOIN
                      dbo.DIMENSIONS AS DIMENSIONS_1 ON dbo.PURCHLINE.DIMENSION2_ = DIMENSIONS_1.NUM LEFT OUTER JOIN
                      dbo.EMPLTABLE ON dbo.PURCHTABLE.PURCHPLACER = dbo.EMPLTABLE.EMPLID LEFT OUTER JOIN
                      dbo.INVENTDIM ON dbo.PURCHLINE.INVENTDIMID = dbo.INVENTDIM.INVENTDIMID LEFT OUTER JOIN
                      dbo.PROJVISITLOADS ON dbo.PURCHTABLE.PURCHID = dbo.PROJVISITLOADS.HAULAGEPO
           INNER JOIN dbo.CUSTPACKINGSLIPTRANS ON dbo.PROJVISITLOADS.PACKINGSLIPID = dbo.CUSTPACKINGSLIPTRANS.PACKINGSLIPID
           INNER JOIN dbo.CUSTPACKINGSLIPJOUR ON dbo.PROJVISITLOADS.PACKINGSLIPID = dbo.CUSTPACKINGSLIPJOUR.PACKINGSLIPID
WHERE     (dbo.EMPLTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTDIM.DATAAREAID = N'agp') AND 
                      (dbo.PURCHTABLE.DATAAREAID = N'agp') AND (dbo.PURCHLINE.DATAAREAID = N'agp') AND (DIMENSIONS_1.DATAAREAID = N'agp') AND 
                      (DIMENSIONS_1.DIMENSIONCODE = 1) AND (dbo.VENDTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTPOSTING.DATAAREAID = N'agp') AND 
                      (dbo.INVENTPOSTING.INVENTACCOUNTTYPE = 0) AND (dbo.LEDGERTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLEMODULE.MODULETYPE = 0) AND 
                      (dbo.INVENTTABLEMODULE.DATAAREAID = N'vuk') AND (dbo.CUSTPACKINGSLIPTRANS.DATAAREAID = N'agp') AND (dbo.CUSTPACKINGSLIPJOUR.DATAAREAID = N'agp')

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-04 : 08:30:35

[code]SELECT *
FROM (SELECT <Your Columns List what ever in your above query>
, ROW_NUMBER() OVER(PARTITION BY PACKINGSLIPID ORDER BY ColumnNameOnwhichBasisYouWantFirstRow ) RN
FROM ...........................
) Temp
WHERE Temp.RN=1[/code]

--
Chandu

runnerpaul
Starting Member

24 Posts

Posted - 2013-09-05 : 03:59:33

Perhaps what I was looking to do was wrong. The query now only returns 5060 records. That's the same number of records that CUSTPACKINGSLIPTRANS gives me when I search for the first record for each PACKINGSLIPID.

Perhaps the way I'm going about this is incorrect. Let me try to explain what I'm trying to do.

Below is my original query:

SELECT     TOP (100) PERCENT dbo.PURCHLINE.DIMENSION3_ AS [Cost Centre], CASE WHEN Month(dbo.PURCHLINE.DELIVERYDATE) 
                      <= 2 THEN year(dbo.PURCHLINE.DELIVERYDATE) - 1 WHEN Month(dbo.PURCHLINE.DELIVERYDATE) >= 4 THEN year(dbo.PURCHLINE.DELIVERYDATE) 
                      WHEN Month(dbo.PURCHLINE.DELIVERYDATE) = 3 AND year(dbo.PURCHLINE.DELIVERYDATE) <= 2011 THEN year(dbo.PURCHLINE.DELIVERYDATE) 
                      ELSE year(dbo.PURCHLINE.DELIVERYDATE) - 1 END AS [Fin Year], CASE WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2012 AND 
                      MONTH(dbo.PURCHLINE.DELIVERYDATE) <= 2 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 10 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2011 AND 
                      MONTH(dbo.PURCHLINE.DELIVERYDATE) >= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 2 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) = 2012 AND 
                      MONTH(dbo.PURCHLINE.DELIVERYDATE) = 3 THEN 13 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2013 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) 
                      <= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 9 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) 
                      >= 4 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 3 END AS Period, 
                      CASE dbo.PURCHTABLE.PURCHASETYPE WHEN '0' THEN 'Journal' WHEN '1' THEN 'Quotation' WHEN '2' THEN 'Subscription' WHEN '3' THEN 'Purchase Order' WHEN
                       '4' THEN 'Returned Item' WHEN '5' THEN 'Blanket Order' END AS [Order Type], dbo.PURCHTABLE.PURCHID AS [Purchase Order], 
                      dbo.PURCHTABLE.PROJID AS Project, CONVERT(varchar(12), dbo.PURCHTABLE.CREATEDDATE, 111) AS [Trans Date], 
                      dbo.PURCHTABLE.VENDORREF AS [Vendor Requistion], 
                      CASE dbo.PURCHTABLE.PURCHSTATUS WHEN '0' THEN 'None' WHEN '1' THEN 'Open Order' WHEN '2' THEN 'Received' WHEN '3' THEN 'Invoiced' WHEN '4' THEN 'Canceled'
                       END AS [Order Status], dbo.PURCHTABLE.CURRENCYCODE AS [Order Currency], dbo.PURCHTABLE.ORDERACCOUNT AS [Vendor Account], 
                      dbo.PURCHTABLE.INVOICEACCOUNT AS [Invoice Account], dbo.VENDTABLE.NAME AS [Vendor Name], DIMENSIONS_1.DESCRIPTION AS [Product Group], 
                      dbo.INVENTTABLE.ITEMGROUPID AS [Item Group], dbo.INVENTTABLE.SECTION, dbo.PURCHLINE.ITEMID AS [Stock Code], 
                      dbo.INVENTTABLE.ITEMNAME AS [Stock Desc], dbo.PURCHLINE.PURCHUNIT AS [Unit of Purchase], dbo.PURCHLINE.PURCHQTY AS [Qty Ordered], 
                      dbo.PURCHLINE.REMAINPURCHPHYSICAL AS [Qty Remaining], dbo.INVENTTABLE.NETWEIGHT AS [Item Weight], dbo.PURCHLINE.PURCHPRICE AS [Unit Price], 
                      dbo.PURCHLINE.LINEPERCENT AS [Dicount %], dbo.PURCHLINE.LINEDISC AS [Discount Amt], dbo.PURCHLINE.LINEAMOUNT AS [Net Amount(Pos)], 
                      CASE dbo.PURCHLINE.BLOCKED WHEN '0' THEN 'No' WHEN '1' THEN 'Yes' END AS [Order Line Stopped?], dbo.PURCHLINE.DIMENSION AS [Site/Location], 
                      dbo.PURCHLINE.DIMENSION4_ AS Department, dbo.PURCHLINE.DIMENSION6_ AS [Job Number], dbo.INVENTDIM.INVENTLOCATIONID AS Warehouse, 
                      dbo.EMPLTABLE.NAME AS [Sales Person], CONVERT(varchar(12), dbo.PURCHLINE.DELIVERYDATE, 111) AS [Delivery Date], 
                      dbo.INVENTPOSTING.LEDGERACCOUNTID AS [GL Code], dbo.LEDGERTABLE.ACCOUNTNAME AS [GL Name], 
                      dbo.INVENTTABLEMODULE.PRICE AS [Standard Cost Price], dbo.PROJVISITLOADS.LOADWEIGHT AS [Load Weight]
FROM         dbo.VENDTABLE INNER JOIN
                      dbo.PURCHLINE INNER JOIN
                      dbo.INVENTTABLE ON dbo.PURCHLINE.ITEMID = dbo.INVENTTABLE.ITEMID INNER JOIN
                      dbo.PURCHTABLE ON dbo.PURCHLINE.PURCHID = dbo.PURCHTABLE.PURCHID AND dbo.PURCHLINE.DATAAREAID = dbo.PURCHTABLE.DATAAREAID ON 
                      dbo.VENDTABLE.ACCOUNTNUM = dbo.PURCHTABLE.ORDERACCOUNT INNER JOIN
                      dbo.INVENTPOSTING ON dbo.INVENTTABLE.ITEMGROUPID = dbo.INVENTPOSTING.ITEMRELATION INNER JOIN
                      dbo.LEDGERTABLE ON dbo.INVENTPOSTING.LEDGERACCOUNTID = dbo.LEDGERTABLE.ACCOUNTNUM INNER JOIN
                      dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID LEFT OUTER JOIN
                      dbo.DIMENSIONS AS DIMENSIONS_1 ON dbo.PURCHLINE.DIMENSION2_ = DIMENSIONS_1.NUM LEFT OUTER JOIN
                      dbo.EMPLTABLE ON dbo.PURCHTABLE.PURCHPLACER = dbo.EMPLTABLE.EMPLID LEFT OUTER JOIN
                      dbo.INVENTDIM ON dbo.PURCHLINE.INVENTDIMID = dbo.INVENTDIM.INVENTDIMID LEFT OUTER JOIN
                      dbo.PROJVISITLOADS ON dbo.PURCHTABLE.PURCHID = dbo.PROJVISITLOADS.HAULAGEPO
WHERE     (dbo.EMPLTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTDIM.DATAAREAID = N'agp') AND 
                      (dbo.PURCHTABLE.DATAAREAID = N'agp') AND (dbo.PURCHLINE.DATAAREAID = N'agp') AND (DIMENSIONS_1.DATAAREAID = N'agp') AND 
                      (DIMENSIONS_1.DIMENSIONCODE = 1) AND (dbo.VENDTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTPOSTING.DATAAREAID = N'agp') AND 
                      (dbo.INVENTPOSTING.INVENTACCOUNTTYPE = 0) AND (dbo.LEDGERTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLEMODULE.MODULETYPE = 0) AND 
                      (dbo.INVENTTABLEMODULE.DATAAREAID = N'vuk')

When I run it I get just over 71000 rows.

I now want to add a a column from a different table (dbo.CUSTPACKINGSLIPTRANS.DIMENSION2_). If I add this as an INNER JOIN my query returns over 86000. I tried different join types but the row counts were still out. I'm looking to get the same number of rows returned but just have the extra column.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-06 : 01:43:35

What Bandi said will work to make sure you only pick one row. The fact you're getting more than one says you're not doing that ROW_NUMBER() thing.
If you don't have at least one row in the CUSTPACKINGSLIPTRANS table for each row in in the source table then you will lose rows and you'll have to do an outer join and deal with NULLS where the row is missing.
And drop the 100 percent - pointless.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources