Answers
Aug 05, 2008 - 05:24 PM
I don't quite understand. Is field-1a and field-2a the only fields in the tables? Or is there a key where you can match the records? I assume that field-1a and field-2a are the only fields in he table. I created two tables like so:
CREATE TABLE [dbo].[Table_1](
[field-1a] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (1)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (2)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (3)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (6)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (8)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (9)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (10)
GO
GO
CREATE TABLE [dbo].[Table_2](
[field-2a] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (3)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (6)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (9)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (12)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (13)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (15)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (11)
GO
If you then do
SELECT DISTINCT [field-1a] as 'field-3a'
from dbo.Table_1
UNION
Select DISTINCT [field-2a] as 'field-3a'
from dbo.Table_2
You get
1
10
11
12
13
15
2
3
6
8
9
Is this what you wanted? If you have other columns in the table and you need to match them by a key field I recommend using JOIN.
Regards
Peter
CREATE TABLE [dbo].[Table_1](
[field-1a] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (1)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (2)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (3)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (6)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (8)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (9)
INSERT INTO [TestDb].[dbo].[Table_1]([field-1a]) VALUES (10)
GO
GO
CREATE TABLE [dbo].[Table_2](
[field-2a] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (3)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (6)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (9)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (12)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (13)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (15)
INSERT INTO [TestDb].[dbo].[Table_2]([field-2a]) VALUES (11)
GO
If you then do
SELECT DISTINCT [field-1a] as 'field-3a'
from dbo.Table_1
UNION
Select DISTINCT [field-2a] as 'field-3a'
from dbo.Table_2
You get
1
10
11
12
13
15
2
3
6
8
9
Is this what you wanted? If you have other columns in the table and you need to match them by a key field I recommend using JOIN.
Regards
Peter
Aug 06, 2008 - 04:09 AM
ALTER PROCEDURE [dbo].[usp_GetCardHolderInfo]
(
@EmployeeKey INT
)
AS
SET NOCOUNT ON
SELECT *
----Case uvw_cc_division_Transfer.transfer_division
----When '' Then cc_division.division
----Else uvw_cc_division_Transfer.transfer_division
----End NewOldDiv
FROM
cc_employee
LEFT JOIN
cc_division ON cc_employee.division_id = cc_division.division_id
LEFT JOIN
cc_classification ON cc_employee.class_id = cc_classification.class_id
LEFT JOIN
cc_employee_type ON cc_employee.employee_type_id = cc_employee_type.employee_type_id
LEFT JOIN
cc_district ON cc_employee.district_id = cc_district.district_id
LEFT JOIN
uvw_cc_division_transfer ON cc_employee.employee_key = uvw_cc_division_Transfer.employee_key
WHERE
cc_employee.employee_key = @EmployeeKey
Thanks Peter. The above is the code for it. The original sproc contains other tables and details. The transfer_divsion exists on table-1 and possibly on table 2. I need to select it from table-2 (if that does not exist on table-2, then use the one on table-1).
(
@EmployeeKey INT
)
AS
SET NOCOUNT ON
SELECT *
----Case uvw_cc_division_Transfer.transfer_division
----When '' Then cc_division.division
----Else uvw_cc_division_Transfer.transfer_division
----End NewOldDiv
FROM
cc_employee
LEFT JOIN
cc_division ON cc_employee.division_id = cc_division.division_id
LEFT JOIN
cc_classification ON cc_employee.class_id = cc_classification.class_id
LEFT JOIN
cc_employee_type ON cc_employee.employee_type_id = cc_employee_type.employee_type_id
LEFT JOIN
cc_district ON cc_employee.district_id = cc_district.district_id
LEFT JOIN
uvw_cc_division_transfer ON cc_employee.employee_key = uvw_cc_division_Transfer.employee_key
WHERE
cc_employee.employee_key = @EmployeeKey
Thanks Peter. The above is the code for it. The original sproc contains other tables and details. The transfer_divsion exists on table-1 and possibly on table 2. I need to select it from table-2 (if that does not exist on table-2, then use the one on table-1).
Add New Comment