August 10, 2009 at 12:37 pm
I have a scenario where I am trying to select various records based on the contents of particular columns. I have an example in the code below
CREATE TABLE #REF
(
Specialty varchar(20)
,Team varchar(20)
,Code1 varchar(20)
,Code2 varchar(20)
,Code3 varchar(20)
)
INSERT INTO #REF
SELECT 'POD', 'HEALTH', 'SS18','SS19','SS19' UNION ALL
SELECT 'POD', 'HEART', 'SS19','SS18','SS20' UNION ALL
SELECT 'POD', 'DEFAULT', null,null,'SS18' union all
SELECT 'POD', 'DEFAULT', null,'SS19','SS18'
With the example above I would like to select records which have the data 'SS18' in either the code1,code2 or code3 fields. However i would like the select to be as follows:
check if 'SS18' is in code1 if so select the record else check code2. If in code2 select record else check code3. if in code3 select record else do not select record.
How can i script that with t-sql?
August 10, 2009 at 12:44 pm
Does this work? I am not sure about your expected results.
SELECT * FROM #REF
WHERE (Code1 = 'SS18' OR Code2 = 'SS18' OR Code3 = 'SS18')
August 10, 2009 at 12:53 pm
As Matt already mentioned ...not sure what you require as results
try this
SELECT Specialty, Team, Code1 AS Code
FROM dbo.#REF
WHERE (Code1 = 'SS18')
UNION
SELECT Specialty, Team, Code2 AS Code
FROM dbo.#REF
WHERE (Code2 = 'SS18')
UNION
SELECT Specialty, Team, Code3 AS Code
FROM dbo.#REF
WHERE (Code3 = 'SS18')
Look up UNION and UNION ALL in BOL
regards gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 10, 2009 at 1:13 pm
This returns the 1st instance of 'SS18':
SELECT top 1 * FROM #REF
WHERE 'SS18' in (Code1, Code2, Code3)
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
August 10, 2009 at 2:23 pm
Sorry I was not clear in my initial explanation of the scenario. The suggestions given would work for the data in the example I gave. However what I want is slightly different. I will try and explain with an example below
CREATE TABLE #REF
(
Specialty varchar(20)
,Team varchar(20)
,Code1 varchar(20)
,Code2 varchar(20)
,Code3 varchar(20)
)
INSERT INTO #REF
SELECT 'POD', 'HEALTH', 'SS18','SS16','SS19' UNION ALL
SELECT 'POD', 'HEART', 'SS17','SS14','SS18' UNION ALL
SELECT 'POD', 'HEART', 'SS18','SS14','SS18' UNION ALL
SELECT 'POD', 'HEART', 'SS16','SS14','SS18' UNION ALL
SELECT 'POD', 'HEART', 'SS16','SS14','SS17' UNION ALL
SELECT 'POD', 'DEFAULT', NULL,NULL,'SS18' UNION ALL
SELECT 'POD', 'DEFAULT', NULL,'SS19','SS18'
I want to pick out all the records that have 'SS18' OR 'SS19' in the code1,code2 or code3 fields. It should check code1 for 'SS18' OR 'SS19',
if present select the record. If not then check code2 for 'SS18' OR 'SS19'. If present then select the record. If not then check code3 for 'SS18' OR 'SS19' If present then select the record. If not then do not select the record.
August 10, 2009 at 2:42 pm
Using the same logic as the 2 different approaches above you will get 2 different result sets:
[Code]
SELECT * FROM #REF
WHERE (Code1 in ('SS18','SS19') OR Code2 in ('SS18','SS19') OR Code3 in ('SS18','SS19'))
[/code]
SELECT Specialty, Team, Code1 AS Code
FROM dbo.#REF
WHERE (Code1 in ('SS18','SS19'))
UNION
SELECT Specialty, Team, Code2 AS Code
FROM dbo.#REF
WHERE (Code2 IN ('SS18','SS19'))
UNION
SELECT Specialty, Team, Code3 AS Code
FROM dbo.#REF
WHERE (Code3 IN ('SS18','SS19'))
August 10, 2009 at 2:56 pm
can you please us show the results you are expecting
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 10, 2009 at 2:57 pm
if present select the record. If not then check code2 for 'SS18' OR 'SS19'. If present then select the record. If not then check code3 for 'SS18' OR 'SS19' If present then select the record. If not then do not select the record.
SELECT top 1 * FROM #REF
WHERE 'SS18' in (Code1, Code2, Code3)
OR 'SS19' in (Code1, Code2, Code3)
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
August 10, 2009 at 3:13 pm
what i am trying to pull out is a dataset where I do not have duplicate records because, for example, the record has 'SS18' in code1 and 'SS18' in code3.
August 10, 2009 at 3:28 pm
re:
what i am trying to pull out is a dataset where I do not have duplicate records because, for example, the record has 'SS18' in code1 and 'SS18' in code3.
Why does this not meet your reqs?
SELECT top 1 * FROM #REF
WHERE 'SS18' in (Code1, Code2, Code3)
OR 'SS19' in (Code1, Code2, Code3)
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
August 10, 2009 at 3:42 pm
HI sorry it does answer my query. I was answering the forum on my blackberry so I was away for sql client. I am back at the computer and it is working now. Many thanks.
August 10, 2009 at 3:46 pm
what was the correct answer?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply