or:

SELECT RoomType

FROM MyTable T

    JOIN (

        SELECT 1 UNION ALL

        SELECT 2 UNION ALL

        SELECT 4

    ) D (Amenity)

    ON T.Amenity = D.Amenity

GROUP BY RoomType

HAVING COUNT(*) = 3

As you have seen, manipulating strings is best done on the client or middle tier. If you really must do this in SQL then the following may be slightly more...

Something like the following may help:

SELECT DISTINCT

    n.cust_id

    ,es.er_id

    ,n.name_id

    ,n.first_name

    ,n.mid_name

    ,n.last_name

    ,n.name_gen

    ,es.cust_id as conflict_cust_id

FROM [name] n

    JOIN er_cust_state es

        ON n.dsrc_acct_id = es.dsrc_acct_id

    JOIN (

            SELECT es2.cust_id, es2.er_id, MAX(n2.name_id) AS name_id

            FROM [name] n2

                JOIN er_cust_state es2

                    ON n2.dsrc_acct_id = es2.dsrc_acct_id

                LEFT JOIN...

Without DDL it is difficult to tell. Maybe something like:

UPDATE S

SET qty_in_stock = qty_in_stock - D.orders

FROM sku S

    JOIN (

            SELECT S1.sku, S1.labsupplied ,COUNT(*) AS orders

            FROM orders O1

                JOIN sku S1

                    ON O1.framestylecode =...

This sounds like parameter sniffing. The first time the stored procedure is compiled it uses the parameters which were passed to it to generate the most efficient query plan for those...

I was thinking of a left semi join operator which should require less reads than a join; especially if there are a lot of FKs in TableB for each PK in...

My experience in SQL2000 is that joins in subqueries always produce nested loops. I therefore suspect that Sergiy's LEFT JOIN solution, or something like the following, will be more efficient.

SELECT...

Try replacing * with %.

In the absence of more information, here is an outline of something that may work.

If it does work, getting rid of the functions should speed it up.

CREATE TABLE #Value

(

    Value int...

Is Value, Perf_YTD unique?

What is the PRIMARY KEY of the table?

The following non-ANSI t-sql should do it:

DECLARE @a char(6)

    ,@b varchar(6)

SET @a = '123456'

SET @b-2 = ''

SELECT @b-2 = @b-2 + D.Number

FROM (

        SELECT CAST (

                    CASE D1.Number

                    WHEN 9 THEN 0

                    ELSE D1.Number...

My example was poor.

The potential problem is when the derived table references the table to be updated. If this is the case then an UPDLOCK needs to be applied in...

Using NOLOCK on an UPDATE statement does not make sense as an XLOCK will be obtained on the table to be updated. To avoid deadlocks UPLOCKs should be applied to all...

What does the full UPDATE statement look like?

Does it contain a derived table?

Your Enterprise Manager Designer fiddle obviously does not cope with derived tables. If you want to do this you will have to do the derived tables separately.

I am not convinced...