How to optimize or remove redundancy from following query

Question

I have 4 tables

Table1 (employee)
id          name
--------------------
1           a
2           b

Table2 (appointment)
id    table1id    table3id    table4id   sdate    edate     typeid
-----------------------------------------------------------------------------------
1       1              1          1      1/1/09    NULL       100
2       2              2          1      1/1/09    NULL       101


Table3 (title)
id      name
---------------
1       worker1
2       worker2
3       Assistant
4       Manager

Table4 (Department names)
id      name
-------------------
1       Logistics
2       ABC
3       XYZ

Type
id       name
----------------
100      w (primary)
101      e (secondary)
102      r (other-primary)
103      t (.....)
104      y (....)

To avoid dups I'm writing query as

Select id, name, title, dept
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1id
INNER JOIN table3 c ON b.table3id = c.id
INNER JOIN table4 d ON d.id = b.table4id
WHERE typeid =
        (
           SELECT min(type_id) /* i want primary type appointments */
           FROM table2
           WHERE sdate < getdate() and (edate > getdate() or edate IS NULL)
           AND sdate = (select max(sdate) from table2 where table1id = a.id)
           AND typeid in (100, 102)
        )
AND b.sdate < getdate() and (b.edate > getdate() or b.edate IS NULL)
AND b.sdate = (select max(sdate) from table2 where table1id = a.id)

/* last two i have to repeat again to remove dupes */

Is there a way i can reduce using same condition twice and query it specifying only once or any other better way? AND typeid in (100, 102)

Answer 1

Would something like this be an improvement, using a sub-query in the join to get the numbers you want?

Select id, name, title, dept
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1id
INNER JOIN table3 c ON b.table3id = c.id
INNER JOIN table4 d ON d.id = b.table4id
INNER JOIN (select max(sdate) from table2 group by table1id) new1 ON new1.table1id = a.id
WHERE typeid =
    (
       SELECT min(type_id) /* i want primary type appointments */
       FROM table2
       WHERE sdate < getdate() and (edate > getdate() or edate IS NULL)
       AND sdate = (select max(sdate) from table2 where table1id = a.id)
       AND typeid in (100, 102)
    )
AND b.sdate < getdate() and (b.edate > getdate() or b.edate IS NULL)
AND b.sdate = new1.sdate

You might also try looking into the HAVING clause of GROUP BY. I think you would be able to do something like:

Select id, name, title, dept
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1id
INNER JOIN table3 c ON b.table3id = c.id
INNER JOIN table4 d ON d.id = b.table4id
WHERE b.sdate < getdate() and (b.edate > getdate() or b.edate IS NULL)
AND typeid in (100, 102)
GROUP BY id, name, title, dept
HAVING b.sdate = max(sdate)
  AND typeid = min(type_id)

However the above might pull the min and max for the entire list, not for each a.id. I forget if you can use a partition in the max to specify what to max over, or if I am thinking of Oracle there. If not, you could always use a sub-query that gets the best entry for each a.id.

Answer 2

I used same query and it is working fast, I didn't find any other way to optimize it