Select all rows with same ID when 1 column meets criteria

Question

I need help with a SQL query. My problem is better explained with some examples.

Say I have my data like this pulling from two tables, ORDR and RDRO. The first table (ORDR) contains all the data about the order except for the items. The second table (RDRO) contains all the item information for an order. Both tables can be joined using [docNum].

T0.docNum | T1.itemCode
123         EDS100
123         DDS200
123         DDS500

124         LLS220
124         LDS254

125         FGD450
125         LDS552
125         DDS200 
125         EDD200

I want to return all rows when 1 item number meets the criteria.

Say in this case I want all the rows of the order when one row is equal to DDS200 would give this result:

T0.docNum | T1.itemCode
123         EDS100
123         DDS200    -- match
123         DDS500

125         FGD450
125         LDS552
125         DDS200    -- match
125         EDD200

I'm sure this is trivial, I have tried the following:

SELECT T0.docNum, T1.itemCode 
FROM ordr T0  INNER JOIN rdro T1 ON T0.docNum = T1.docNum 
WHERE  T1.itemCode like 'DDS200';

this only returns the rows that meet the criteria, not all the rows of the order as shown above.

Also, please note I'm on SQL server.

Thanks for your help

Answer 1

There are a few ways that you can achieve this...

You can use a subquery to return the docNum of the rows that match the criteria from RDRO and then use an outer query to get the rest; like so:

SELECT r.docNum, r.itemCode
  FROM (SELECT DISTINCT docNum
          FROM rdro
         WHERE itemCode = 'DDS200') AS a
  JOIN rdro AS r
    ON r.docNum = a.docNum;

Or, as pointed out by Aaron, you can also use a WHERE EXISTS to achieve the same result:

SELECT r.docNum, r.itemCode
  FROM rdro as r
 WHERE EXISTS (
         SELECT * 
           FROM rdro 
          WHERE docNum = r.docNum 
            AND itemCode = 'DDS200');

Or you can use a WHERE...IN:

SELECT r.docNum, r.itemCode
  FROM rdro as r
 WHERE r.docNum IN (
         SELECT docNum
           FROM rdro 
          WHERE itemCode = 'DDS200');

Or you could even do it straight in the JOIN:

SELECT r.docNum, r.itemCode
  FROM rdro as r
  JOIN rdro AS o
    ON r.docNum = o.docNum
   AND o.itemCode = 'DDS200';

Heck, for completeness lets even throw in a CTE version:

 WITH docs
 AS
 (SELECT docNum FROM RDRO WHERE itemCode = 'DDS200')
  SELECT r.docNum, r.itemCode
    FROM RDRO AS r
    JOIN docs AS d
      ON r.docNum = d.docNum;

Try each one and review the execution plans that are generated for them before choosing which one to go for. Normally if two query plans are the same I choose the query that is easier to maintain.

Example of execution and results can be found on sqlfiddle.