SQL Server 2000 – Select Rows Matching Condition with Default

sql serversql-server-2000

I have a table

id   |  title             | lng        |  vi_id
--------------------------------------------------------
1    | 'IX#001323'        |         1  | 12
2    | 'IX#001323 heb'    |         2  | 12
3    | 'IX#001544'        |         1  | 22
4    | 'IX#001666'        |         1  | 34
5    | 'IX#001666 heb'    |         2  | 34
6    | 'IX#002100'        |         1  | 55
7    | 'IX#002100 heb'    |         2  | 55
8    | 'IX#002100 spa'    |         3  | 55

I pass in a parameter @lng

For each vi_id I need to get the row matching that lng value if it exists or default to the row with lng =1 if it doesn't.

for @lng = 2

id   |  title           | lng       |  vi_id
--------------------------------------------------------
2    | 'IX#001323 heb'  |         2  | 12
3    | 'IX#001544'      |         1  | 22
5    | 'IX#001666 heb'  |         2  | 34
7    | 'IX#002100 heb'  |         2  | 55

for @lng = 3

id   |  title           | lng       |  vi_id
--------------------------------------------------------
2    | 'IX#001323'      |         1  | 12
3    | 'IX#001544'      |         1  | 22
5    | 'IX#001666'      |         1  | 34
8    | 'IX#002100 spa'  |         3  | 55

i tried to do inner join with the same table but i get all rows with another column of the second lng

sqlfiddle

Best Answer

Here are a couple of approaches you can try.

DECLARE @lng INT;

SET @lng = 3;

/*Approach 1*/
SELECT y.*
FROM   YourTable AS y
WHERE  y.lng IN (1,@lng)
       AND y.lng = (SELECT MAX(y2.lng)
                    FROM   YourTable AS y2
                    WHERE  y2.vi_id = y.vi_id
                           AND y2.lng IN (1,@lng))
ORDER BY id; 


/*Approach 2 (assumes id is PK and all vi_id have a lng=1 row) */
SELECT 
    CASE WHEN y2.id IS NULL THEN y1.id ELSE y2.id END AS id,
    CASE WHEN y2.id IS NULL THEN y1.title ELSE y2.title END AS title,
    CASE WHEN y2.id IS NULL THEN y1.lng ELSE y2.lng END AS lng,
    CASE WHEN y2.id IS NULL THEN y1.vi_id ELSE y2.vi_id END AS vi_id
FROM   YourTable y1
       LEFT JOIN YourTable y2
         ON y1.vi_id = y2.vi_id
            AND y2.lng = @lng
WHERE  y1.lng = 1 
ORDER BY id;
Related Question