June 3, 2015 at 3:45 am
I have a table with fields FeeId and Amount as below. FeeID is a dynamic value..
FeeID Amount
1 100
2 150
3 200
4 250
5 300
I have another table with fields EmpId and FeeID as below
EmpIDFeeID
1 1
1 2
1 3
2 1
2 4
3 3
3 5
Required output
EmpID FeeID Amount
1 1 100
1 2 150
1 3 200
1 4 0
1 5 0
2 1 100
2 2 0
2 3 0
2 4 250
2 5 0
3 1 0
3 2 0
3 3 200
3 4 0
3 5 300
June 3, 2015 at 4:55 am
Please post data in a consumable way.
Show us what you have tried.
See here for posting guidelines: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
You're just a few clicks away from a good question!
-- Gianluca Sartori
June 3, 2015 at 5:28 am
To provide an actual, tested query, we'll also need to know the table names. This is pretty simple, so I'd also like to see what you've tried so far.
June 3, 2015 at 5:50 am
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[allowances]') AND type in (N'U'))
DROP TABLE [dbo].[allowances]
GO
CREATE TABLE [dbo].[allowances](
[allid] [int] NULL,
[name] [nchar](10) NULL,
[amount] [int] NULL
) ON [PRIMARY]
GO
insert into allowances values(1,'a',10)
insert into allowances values(2,'b',20)
insert into allowances values(3,'c',25)
insert into allowances values(4,'d',30)
insert into allowances values(5,'e',35)
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[allowance]') AND type in (N'U'))
DROP TABLE [dbo].[allowance]
GO
CREATE TABLE [dbo].[allowance](
[empid] [int] NULL,
[allowanceid] [int] NULL
) ON [PRIMARY]
GO
insert into allowance values(1,1)
insert into allowance values(1,2)
insert into allowance values(1,3)
insert into allowance values(2,1)
insert into allowance values(2,4)
insert into allowance values(3,3)
insert into allowance values(3,5)
go
--I tried
select distinct allowance.empid,allowances.allid,allowances.amount
from allowances left outer join allowance on allowances.allid=allowance.allowanceid
order by empid
--Needed as below
EmpID FeeID Amount
1 1 10
1 2 20
1 3 25
1 4 0
1 5 0
2 1 10
2 2 0
2 3 0
2 4 30
2 5 0
3 1 0
3 2 0
3 3 25
3 4 0
3 5 35
June 3, 2015 at 6:43 am
You need to make a Cartesian of the 2 tables and then left join them, like this
WITH a AS (
SELECT DISTINCT allowance.empid,allowances.allid
FROM allowance
CROSS JOIN allowances
)
SELECT a.empid,a.allid,ISNULL(allowances.amount,0) AS [amount]
FROM a
LEFT JOIN allowance ON allowance.empid = a.empid
AND allowance.allowanceid = allid
LEFT JOIN allowances on allowances.allid=allowance.allowanceid
ORDER BY empid ASC,allid ASC;
Far away is close at hand in the images of elsewhere.
Anon.
June 3, 2015 at 6:48 am
thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply