January 24, 2011 at 4:25 pm
Hi, below are the create table, the insert statement to insert some test records, and a query.
CREATE TABLE [dbo].[tblCost](
[Unit] [varchar](6) NOT NULL,
[ApproverID] [char](6) NULL,
) ON [PRIMARY]
delete from [tblCost]
CREATE TABLE [dbo].[tblApprover](
[Unit] [varchar](6) NOT NULL,
[ApproverID] [char](6) NULL,
) ON [PRIMARY]
INSERT INTO [tblCost] (Unit, ApproverID)
VALUES ('12345', 'Pa0004'),
('23456', 'Pa0002')
INSERT INTO [tblApprover] (Unit, ApproverID)
VALUES ('12345', 'Pa0001'),
('12345', 'Pa0002'),
('12345', 'Pa0003'),
('23456', 'Pa0002')
This is my query
SELECT
C.Unit,
A.ApproverID
FROM tblCost C
JOIN tblApprover A
ON C.Unit = A.Unit
where C.Unit = '12345'
Unit ApproverId
12345Pa0001
12345Pa0002
12345Pa0003
(I need to see the result below)
Unit ApproverId
12345Pa0001
12345Pa0002
12345Pa0003
12345 Pa0004 (from tblCost)
What type of Join do I need to use to ge the result I need?
Thanks,
January 24, 2011 at 4:38 pm
Something like this:
CREATE TABLE [dbo].[tblCost](
[Unit] [varchar](6) NOT NULL,
[ApproverID] [char](6) NULL,
) ON [PRIMARY]
delete from [tblCost];
CREATE TABLE [dbo].[tblApprover](
[Unit] [varchar](6) NOT NULL,
[ApproverID] [char](6) NULL,
) ON [PRIMARY];
INSERT INTO dbo.[tblCost](Unit, ApproverID)
VALUES ('12345', 'Pa0004'),
('23456', 'Pa0002');
INSERT INTO dbo.[tblApprover](Unit, ApproverID)
VALUES ('12345', 'Pa0001'),
('12345', 'Pa0002'),
('12345', 'Pa0003'),
('23456', 'Pa0002');
WITH UnitApprovers as (
SELECT
C.Unit,
C.ApproverID
FROM
dbo.tblCost C
UNION
SELECT
A.Unit,
A.ApproverID
FROM
dbo.tblApprover A
)
select
UA.Unit,
UA.ApproverID
from
UnitApprovers UA
where
UA.Unit = 12345;
DROP TABLE [dbo].[tblCost];
DROP TABLE [dbo].[tblApprover];
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply