Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Are you really using SQL Server 2000? Do you have to be? This is very easy to solve in modern versions of SQL Server (e.g. any version created in the last 10 years). On 2000 it is much more complex and likely very inefficient as the table size gets larger. –  Aaron Bertrand Jan 5 at 15:09
    
@AaronBertrand yes it's sql server 2000, the table it's not large, and for now i can't move the DB to another server, it think that even in 2000 they nedded such as query :) –  eyalb Jan 5 at 15:14
    
@MartinSmith i can't see the similar. –  eyalb Jan 5 at 15:16
1  
1  
Well actually if you just do WHERE lng IN (1,3) that dupe should still work for you. sqlfiddle.com/#!6/92665/6 –  Martin Smith Jan 5 at 15:43

1 Answer 1

up vote 1 down vote accepted

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; 
share|improve this answer
    
i prefer the 1 approch because you dont need to add any column, –  eyalb Jan 6 at 7:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.