August 11, 2003 at 8:20 pm
I have two tables that are the same structure and I want to select columns from each of the tables based on the contents of information in the first table. Basically the first table is a list of individuals with certain fields. The second table is a list of defaults for some of the fields. Based on a column in the first table I either want to select the data from the first table or the second table.
This can be accomplished using a join on the tables and a lot of case statements, but that does not seem like the most efficient way to perform this task.
Assuming my table structure contains FirstName,LastName,UseDefault,Field1,Field2
I want to select firstname,lastname,field1, and field2 from the tables and I want field1 and field2 to come from table1 if UseDefault is false and from table2 if UseDefault is true. My table is on a much larger scale than this example and contains several usedefault columns which each correspond to several columns in each table.
August 12, 2003 at 5:52 am
Hi davebray,
quote:
This can be accomplished using a join on the tables and a lot of case statements, but that does not seem like the most efficient way to perform this task.Assuming my table structure contains FirstName,LastName,UseDefault,Field1,Field2
I want to select firstname,lastname,field1, and field2 from the tables and I want field1 and field2 to come from table1 if UseDefault is false and from table2 if UseDefault is true. My table is on a much larger scale than this example and contains several usedefault columns which each correspond to several columns in each table.
not sure if I understand you fully, but can't you evaluate some input parameter within a IF structure like
CREATE PROCEDURE dbo.fai_getMailVaryingSubjects_Attach @sender_name varchar(100), @subject varchar(150), @iMode int AS
IF @iMode = 1
BEGIN
SELECT TOP 200
MsgSubject
AS
Titel, ID
FROM
mails_header
WHERE
((MsgOrigDisplayName= +@sender_name) AND (MsgSubject LIKE @subject))
ORDER BY
MsgDateReceived
DESC
END
ELSE
SELECT
MsgSubject
AS
Titel, ID
FROM
mails_header
WHERE
((MsgOrigDisplayName= +@sender_name) AND (MsgSubject LIKE @subject))
ORDER BY
MsgDateReceived
DESC
GO
Is this going in your direction?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 12, 2003 at 6:58 am
This method would work if I was looking them up one at a time and there was only one default flag in the table, but there is a default flag in each record and it needs to make the decision which items to show based on that flag.
For example, if the table contained:
Fred,Smith,true
Dave,Jones,false
I would want to display
Fred Smith, t2_field1, t2_field2
Dave Jones,t1_field1,t1_field2
In my table there are multiple independent boolean values that make certain fields come from either table 1 or table 2.
August 12, 2003 at 7:42 am
what about using a case structure?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 12, 2003 at 9:02 am
I have it working using a case structure right now, but it just does not seem to be like the most efficient way it could be done. I end up needing a case for each field and it gets a little messy.
August 12, 2003 at 10:11 am
Could you post an example of your code for us to look at?
August 13, 2003 at 12:28 pm
This is a shortened version of the working code. In the full version there are a lot more CASE statements for each section.
CREATE PROCEDURE dbo.sp_employeecalcfields(@Company int,@EmployeeId bigint)
AS
SELECT
-- Calculations Default
SUBSTRING(EL.UseDef,2,1) AS DefaultCalculations,
CASE SUBSTRING(EL.UseDef,2,1) WHEN '1' THEN ET.Salary ELSE EL.Salary END AS Salary,
-- Restrictions Default
SUBSTRING(EL.UseDef,3,1) AS DefaultRestrictions,
CASE SUBSTRING(EL.UseDef,3,1) WHEN '1' THEN ET.ResInFlags ELSE EL.ResInFlags END AS ResInFlags,
CASE SUBSTRING(EL.UseDef,3,1) WHEN '1' THEN ET.ResInHours ELSE EL.ResInHours END AS ResInHours,
-- Scheduler Defaults
SUBSTRING(EL.UseDef,4,1) AS DefaultScheduler,
CASE SUBSTRING(EL.UseDef,4,1) WHEN '1' THEN ET.FlagMins0 ELSE EL.FlagMins0 END AS FlagMins0,
CASE SUBSTRING(EL.UseDef,4,1) WHEN '1' THEN ET.FlagMins1 ELSE EL.FlagMins1 END AS FlagMins1,
CASE SUBSTRING(EL.UseDef,4,1) WHEN '1' THEN ET.FlagMins2 ELSE EL.FlagMins2 END AS FlagMins2,
CASE SUBSTRING(EL.UseDef,4,1) WHEN '1' THEN ET.FlagMins3 ELSE EL.FlagMins3 END AS FlagMins3
FROM EmployeeList EL
LEFT JOIN EmployeeTemplate ET on ET.employeeid=10000000000 and ET.Company=EL.Company
WHERE EL.Company=@Company AND EL.EmployeeId=@EmployeeId
GO
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply