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
Best Answer
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 fromRDRO
and then use an outer query to get the rest; like so:Or, as pointed out by Aaron, you can also use a
WHERE EXISTS
to achieve the same result:Or you can use a
WHERE...IN
:Or you could even do it straight in the
JOIN
:Heck, for completeness lets even throw in a CTE version:
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.