August 11, 2010 at 8:39 pm
Please bear with me as I have been using Microsoft Access for the past 12 years and have finally moved to SQL.
I want to create a query on a table where 25 of the fields are "Yes/No" answers. So I have a lookup table that has 1=Yes and 2=No. In MS Access my fields in datasheet view can be a lookup field but in SQL there is no option that I am aware of. So if a user wants to view the coded value of 1 or 2 that is no problem and if they want to see Yes or No that is also not a problem.
Now that I am in SQL do I have to alias the lookup table 25 times and link the lookup table to the 25 fields so that I can see the Yes and No values from the lookup table or is there a trick that I do not know about.
Any advice would be greatly appreciated!
Owen
Owen White
August 12, 2010 at 7:13 am
I would consider using a BIT field. It uses the values 0 and 1.
Would that work?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 12, 2010 at 7:45 am
This does not work as I have to give the user two value options for export. One option is the actual ID value of 1 or 2 and the other option of the text Yes or No. I cannot be the first person to ever encounter this. I just can't find anything on the internet and so I have turned to the experts to give me their advice.
Owen White
August 12, 2010 at 7:56 am
I would stay away from bit fields and choose a tinyint instead.
It seems that your problem is in the reporting of the values? if this is the case to resolve the value of 1 or 2 to Yes/No you would need to do an inner join between the data table and the lookup table
August 12, 2010 at 9:03 am
I understand that I need to create an inner join with the lookup table however my question is "Do i really need to create 25 inner joins for all 25 Yes/No fields?"
Owen White
August 12, 2010 at 9:12 am
fsuoj (8/12/2010)
I understand that I need to create an inner join with the lookup table however my question is "Do i really need to create 25 inner joins for all 25 Yes/No fields?"
Good question. My initial feeling is to say yes, but I can't help thinking there's a better way.
Can you provide some sample data/code? It'd help if we can visualize the problem.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 12, 2010 at 10:10 am
I am not giving you the exact code from my database but I have created a sample of 5 fields for you:
The lookup table is tblYN and has two columns ID and YesNo where ID is the Primary Key and YesNo is the text choices for the IDs.
Demographics table links to the risk factors table by DID which is the Primary Key of the Demographics table
Each of the fields (not including name) stores the ID from the table tblYN
I want to return the text values from the table tblYN
SELECT LName
,FName
,Smok_YN.YesNo as Smoker
,COPD_YN.YesNo as COPD
,CVA_YN.YesNo as CVA
,CVD_YN.YesNo as CVD
,PVD_YN.YesNo as PVD
FROM Demographics INNER JOIN RiskFactors ON Demographics.DID = RiskFactors.DID
LEFT OUTER JOIN tblYN Smok_YN ON RiskFactors.Smoker = Smok_YN.ID
LEFT OUTER JOIN tblYN COPD_YN ON RiskFactors.COPD= COPD_YN.ID
LEFT OUTER JOIN tblYN CVA_YN ON RiskFactors.CVA= CVA_YN.ID
LEFT OUTER JOIN tblYN CVD_YN ON RiskFactors.CVD= CVD_YN.ID
LEFT OUTER JOIN tblYN PVD_YN ON RiskFactors.PVD= PVD_YN.ID
Owen White
August 12, 2010 at 10:15 am
i would ignore the joins completely and just use a CASE to evaluate each of the yes/nos instead;
don't really think i need to abstract out a Y/N value to a foreign key.
SELECT LName
,FName
,CASE Smok_YN.YesNo WHEN 1 THEN 'Y' ELSE 'N' END as Smoker
,CASE SCOPD_YN.YesNo WHEN 1 THEN 'Y' ELSE 'N' END as COPD
,CASE SCVA_YN.YesNo WHEN 1 THEN 'Y' ELSE 'N' END as CVA
,CASE SCVD_YN.YesNo WHEN 1 THEN 'Y' ELSE 'N' END as CVD
,CASE SPVD_YN.YesNo WHEN 1 THEN 'Y' ELSE 'N' END as PVD
FROM Demographics INNER JOIN RiskFactors ON Demographics.DID = RiskFactors.DID
Lowell
August 12, 2010 at 10:30 am
Good point, but with 25 columns (especially if you repeat that query often), I'd consider using a schemabound scalar udf.
I wouldn't expect that logic to change much, but you never know :w00t:.
August 12, 2010 at 11:28 am
Can you elaborate on the Schema Bound UDF? Are you suggesting that I should create a function that refernces the correct column and return the appropriate value from the that function to he field? I think I like that idea. Do you know if this will give a better or worse performance outcome as I have simplified this scenario to just Yes and No. My lookup table is a Global Lookup table or a MUCK table.
Owen White
August 12, 2010 at 11:33 am
I thought about this however not every field will be yes no. I am not trying to be nit picky and this is my example but I think that the case statement would be more like:
SELECT LName
,FName
,CASE RiskFactor.Smoker WHEN 1 THEN 'Yes' ELSE 'No' END as Smoker
,CASE RiskFactor.COPD WHEN 1 THEN 'Yes' ELSE 'No' END as COPD
,CASE RiskFactor.CVA WHEN 1 THEN 'Yes' ELSE 'No' END as CVA
,CASE RiskFactor.CVD WHEN 1 THEN 'Yes' ELSE 'No' END as CVD
,CASE RiskFactor.PVD WHEN 1 THEN 'Yes' ELSE 'No' END as PVD
FROM Demographics INNER JOIN RiskFactors ON Demographics.DID = RiskFactors.DID
Once again I am just trying to update this in the event someone uses this as an example as I often do.
Thank you,
Owen White
August 12, 2010 at 11:40 am
Here's a version that wouldn't affect performance (or extremly slightly)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.FnYesNo
(
@Value TINYINT
)
RETURNS VARCHAR(3)
WITH SCHEMABINDING
AS
BEGIN
RETURN CASE WHEN @Value <> 0 THEN 'YES' ELSE 'NO' END
END
GO
SELECT dbo.FnYesNo (1)
SELECT dbo.FnYesNo (0)
SELECT dbo.FnYesNo (2)
--DROP FUNCTION dbo.FnYesNo
If you need to get back into another table with a select statement then the performance hit would be massive, I really mean MASSIVE.
August 12, 2010 at 11:42 am
The real advange the the function here is that when (not if but when) the powers that be decide that you need to insert a 3rd value, then all you have to do is take 5 seconds and update the function... rather than rewriting 150 queries.
Of course the same is true if you use a lookup table and that you have more than 2-3 possible values.
August 12, 2010 at 12:16 pm
So what you are saying is that if I change the code that you provided to the following, I would see a poor performance from the system? I anticipate that my lookup table will have close to 10,000 records in it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.FnYesNo
(
@Value TINYINT
)
RETURNS VARCHAR(3)
WITH SCHEMABINDING
AS
BEGIN
RETURN SELECT YesNo FROM tblYN WHERE @Value = tblYN.ID
END
GO
SELECT dbo.FnYesNo (1)
SELECT dbo.FnYesNo (0)
SELECT dbo.FnYesNo (2)
--DROP FUNCTION dbo.FnYesNo
Owen White
August 12, 2010 at 5:06 pm
My code would be fast.
If you were to use that code to do a select in the lookup table then you'd get awful performance. In your case you're better off using a derived table and join to it any number of times you need.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply