Urgent! SQL short Phrase is imposible ? I think not.

[IonelBurtan]

There are two tables :
Table 1(Tab1)
Id int


Table 2(Tab2)
Id int
Type char(1)


Table 1 has a one-to-many relationship with table 2 like this: For each record from table 1 may corespond 1, 2 or 3 records in table 2.

Field Type may have 3 values 'S', 'D', 'I'.

I would like to select one record from table 2 like this:
if exists one record with Type='S' I select this one, if not I select the one with Type = 'D', if this does not exist I select the one with type = 'I' for a given Id from the first table in variable @Id.
So my value order for this field is 'S''D''I'.

How do I do that? I have solve the problem but in a complicated way. I want to do it in a simpler and less full of subqueries way.

Thank you all,
Urgent!!!
s-)

Blessed is he who in the name of justice and goodwill, sheperds the weak through the valley of darkness...

http://www20.brinkster.com/ionelb

 

[tlbroadbent]

I hope I understand your requirement correctly. Try this query and let me know if it works for you. It will only work in SQL 7 or later.

Select t1.Id, Type=
(Select Top 1 Type
From tab2 Where ID=t1.ID
Order By
Case Type
When 'S' Then 1
When 'D' Then 2
Else 3
End)
From tab1 As t1 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[IonelBurtan]

The requirement wasn't exactly like this. I wanted to select a record from the second table not from the first. Your code doesn't compile.

I had managed to solve it using CHARINDEX function. Thank you. Your ideea with a selective orderby is quite good. I don't know how fast it works though.
Below is the solution: (I had replaced a JOIN with @id to make it more readable)


SELECT Id
FROM Tab2 t
WHERE t.Id = @Id and
CHARINDEX(Type,'SDI') = (SELECT MAX(CHARINDEX(Type,'SDI')) FROM tab2 WHERE t.Id = @Id) s-)

Blessed is he who in the name of justice and goodwill, sheperds the weak through the valley of darkness...

http://www20.brinkster.com/ionelb

 

[tlbroadbent]

1) What error do you get when trying to use the query I provided? Which version of SQL Server do you have? As I said, it will only work in SQL 7 or higher. Also, the database compatibility level must be SQL 7 or higher.

2) The query I provided selects Type from table 2. If all you want is the result from table 2 just use the sub-query.

Select Top 1 ID, Type
From tab2 Where ID=@ID
Order By
Case Type
When 'S' Then 1
When 'D' Then 2
Else 3
End

3) Shouldn't your query use MIN rather than MAX if 'SDI' if the order of precedence? Using MAX, if both 'S' and 'I' records exist, the 'I' record will be chosen. Is that what you want or do you want to choose the 'S' record?

SELECT MIN(CHARINDEX(Type,'SDI'))

By the way, I like the concept of your solution. However, I think mine will perform beter on larger tables if you only use the sub-query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.