Select joining table to self a few times? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Select joining table to self a few times?

Author

Topic

mauddib
Starting Member

14 Posts

Posted - 2005-04-20 : 10:46:27

I have a table with about 40 columns in it but I am interested in three. This is a stock report. The are partcode (of the item), location (a 2 character code) and quantity (integer >0).

So if i do this SQL:

SELECT [Item No_], [Location Code], [Remaining Quantity]
FROM dbo.[Live Data$Item Ledger Entry]
WHERE ([Remaining Quantity] > 0)

I get results like:

[Item No_] [Location] [Remaining Quantity]
P8801004 01 3000
P8801004 F1 1000
R7171001 03 1200
R7171001 F1 5000

Which is easy. But I am not good at SQL and what I WANT to write is something that will give me:

[ITEM NO_] [Quantity 01] [Quantity 03] [Quantity F1]
P8801004 3000 NULL 1000
R7171001 NULL 1200 5000

I have about 20 locations but if I can get the idea off someone here for how to do it with three im sure i will extend it myself.

GmcB

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-04-20 : 11:13:11

Does this work?


SELECT dbo.[Live Data$Item Ledger Entry].[Item No_],
       (SELECT a.[Remaining Quantity] FROM dbo.[Live Data$Item Ledger Entry] a WHERE a.[Item No_] = dbo.[Live Data$Item Ledger Entry].[Item No_] AND a.[Location Code] = '01') AS [Quantity 01],
       (SELECT a.[Remaining Quantity] FROM dbo.[Live Data$Item Ledger Entry] a WHERE a.[Item No_] = dbo.[Live Data$Item Ledger Entry].[Item No_] AND a.[Location Code] = '03') AS [Quantity 03],
       (SELECT a.[Remaining Quantity] FROM dbo.[Live Data$Item Ledger Entry] a WHERE a.[Item No_] = dbo.[Live Data$Item Ledger Entry].[Item No_] AND a.[Location Code] = 'F1') AS [Quantity F1]
FROM dbo.[Live Data$Item Ledger Entry]

Dustin Michaels

mauddib
Starting Member

14 Posts

Posted - 2005-04-20 : 11:34:55

Ok I have changed this as its possible that there is more than one entry for each part/location combination. So I have changed it to:

SUM(a.[Remaining Quantity]) instead of a.[Remaining Quantity]

for each occourance. This WORKS and returns the right values, but alas its returning multiple lines of the same results like:

P8801004 Null 20 Null
P8801004 Null 20 Null
P8801004 Null 20 Null
P8801004 Null 20 Null

So where do I put the word UNIQUE to make this work right? I've tried it in a few locations and its not working.

GmcB

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-04-20 : 12:25:41

Try grouping on the item number and location code columns.


SELECT dbo.[Live Data$Item Ledger Entry].[Item No_],
       (SELECT SUM(a.[Remaining Quantity]) FROM dbo.[Live Data$Item Ledger Entry] a WHERE a.[Item No_] = dbo.[Live Data$Item Ledger Entry].[Item No_] AND a.[Location Code] = '01') AS [Quantity 01],
       (SELECT SUM(a.[Remaining Quantity]) FROM dbo.[Live Data$Item Ledger Entry] a WHERE a.[Item No_] = dbo.[Live Data$Item Ledger Entry].[Item No_] AND a.[Location Code] = '03') AS [Quantity 03],
       (SELECT SUM(a.[Remaining Quantity]) FROM dbo.[Live Data$Item Ledger Entry] a WHERE a.[Item No_] = dbo.[Live Data$Item Ledger Entry].[Item No_] AND a.[Location Code] = 'F1') AS [Quantity F1]
FROM dbo.[Live Data$Item Ledger Entry]
GROUP BY dbo.[Live Data$Item Ledger Entry].[Item No_], dbo.[Live Data$Item Ledger Entry].[Location Code]

Dustin Michaels

P.S. You may just want to try seeing if grouping on the Item No column only works too.

mauddib
Starting Member

14 Posts

Posted - 2005-04-20 : 12:35:47

Are you some kind of Genius ? :) hehehehe

Thanks for this, works a treat!!!

GmcB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources