CODEWITHSUNDEEP
sqlsql-servert-sql
Kai
Kai

Reputation: 2987

SQL retrieval from tables

I have a table something like 

EMPLOYEE_ID DTL_ID COLUMN_A COLUMN_B
---------------------------
JOHN        0       1           1
JOHN        1       3           1
LINN        0       1           12
SMITH       0       9           1
SMITH       1       11          12

It means for each person there will be one or more records with different DTL_ID's value (0, 1, 2 .. etc).

Now I'd like to create a T-SQL statement to retrieve the records with EMPLOYEE_ID and DTL_ID.

If the specified DTL_ID is NOT found, the record with DTL_ID=0 will be returned.

I know that I can achieve this in various ways such as checking if a row exists via EXISTS or COUNT(*) first and then retrieve the row.

However, I'd like to know other possible ways because this retrieval statement is very common in my application and my table have hundred thousand of rows.

In the above approach, I've had to retrieve twice even if the record with the DTL_ID specified exists, and I want to avoid this.

Upvotes: 0

Views: 119

Answers (6)

Barry Kaye
Barry Kaye

Reputation: 7759

SELECT MAX(DTL_ID) ...
WHERE DTL_ID IN (@DTL_ID, 0)

Upvotes: 0

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56172

You can use top and union, e.g.:

declare @t table(id int, value int, c char)
insert @t values (1,0,'a'), (1,1,'b'), (1,2,'c')

declare @id int = 1;
declare @value int = 2;

select top(1) *
from
(
    select *
    from @t t
    where t.value =  @value and t.id = @id

    union all

    select *
    from @t t
    where t.value = 0

)a
order by a.value desc

If @value = 2 than query returns 1 2 c. If @value = 3 than query returns 1 0 a.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

If DTL_ID is always 0 or positive:

SELECT TOP 1 * FROM table
where EmployeeID = @EmployeeID and DTL_ID in (@DTL_ID,0)
order by DTL_ID desc

If you're working across multiple employees in a single query, etc, then you might want to use ROW_NUMBER() if your version of SQL supports it.

Upvotes: 2

bniwredyc
bniwredyc

Reputation: 8829

SELECT E1.EMPLOYEE_ID, ISNULL(E2.DTL_ID, 0), E1.COLUMN_A, E1.COLUMN_B EMPLIYEES AS E1
LEFT JOIN EMPLIYEES AS E2
ON E1.EMPLOYEE_ID = E2.EMPLOYEE_ID AND E2.DTL_ID = 42

Upvotes: 0

Ariel
Ariel

Reputation: 26753

Like this:

  SELECT *
    FROM table
   WHERE EMPLOYEE_ID = ?? AND DTL_ID = ??
UNION
  SELECT *
    FROM table
   WHERE EMPLOYEE_ID = ?? AND DTL_ID = 0
     AND NOT EXISTS (SELECT *
                       FROM table
                      WHERE EMPLOYEE_ID = ?? AND DTL_ID = ??)

You will of course have to fill in the ?? with the proper number.

Upvotes: 2

sll
sll

Reputation: 62504

Use ISNULL(DTL_ID, 0) in your final SELECT query

Upvotes: 0

Related Questions