Skip to main content
Database Administrators

Return to Question

Post Reopened by RDFozz, Mr.Brownstone, Aaron Bertrand
per comment I have updated my answer for better clarity
Source Link
Nicolas
Nicolas
  • 41
  • 1
  • 1
  • 4

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].

Doc #T0.docNum | Item #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:

Doc #T0.docNum | Item #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 doc#T0.docNum, item#T1.itemCode 
FROM orderordr T0  INNER JOIN itemsrdro T1 ON T0.docdocNum = T1.docdocNum 
WHERE  T1.item#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

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.

Doc # | Item #
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:

Doc # | Item #
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 doc#, item# 
FROM order T0  INNER JOIN items T1 ON T0.doc = T1.doc 
WHERE  T1.item# like 'DDS200';

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

Thanks for your help

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

Post Closed as "Needs details or clarity" by Aaron Bertrand
format code, format data
Source Link
ypercubeᵀᴹ
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

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.

Doc # | Item #
123     EDS100
123     DDS200
123     DDS500
124     LLS220
124     LDS254
125     FGD450
125     LDS552
125     DDS200
125     EDD200

Doc # | Item #
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:

Doc # | Item #
123     EDS100
123     DDS200
123     DDS500
125     FGD450
125     LDS552
125     DDS200
125     EDD200

Doc # | Item #
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 doc#, item# FROM order T0 INNER JOIN items T1 ON T0.doc = T1.doc WHERE T1.item# like 'DDS200'; <

SELECT doc#, item# 
FROM order T0  INNER JOIN items T1 ON T0.doc = T1.doc 
WHERE  T1.item# like 'DDS200';

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

Thanks for your help

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.

Doc # | Item #
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:

Doc # | Item #
123     EDS100
123     DDS200
123     DDS500
125     FGD450
125     LDS552
125     DDS200
125     EDD200

I'm sure this is trivial, I have tried the following: SELECT doc#, item# FROM order T0 INNER JOIN items T1 ON T0.doc = T1.doc WHERE T1.item# like 'DDS200'; < this only returns the rows that meet the criteria, not all the rows of the order as shown above.

Thanks for your help

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.

Doc # | Item #
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:

Doc # | Item #
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 doc#, item# 
FROM order T0  INNER JOIN items T1 ON T0.doc = T1.doc 
WHERE  T1.item# like 'DDS200';

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

Thanks for your help

corrected based on comment
Source Link
Nicolas
Nicolas
  • 41
  • 1
  • 1
  • 4

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.

Doc # | Item #
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:

Doc # | Item #
123     EDS100
123     DDS200
123     DDS500
125     FGD450
125     LDS552
125     DDS200
125     EDD200

I'm sure this is trivial, I have tried the following: SELECT doc#, item# FROM ordersorder T0 INNER JOIN items T1 ON T0.doc = T1.doc WHERE item# T1.item# like 'DDS200'; < this only returns the rows that meet the criteria, not all the rows of the order as shown above.

Thanks for your help

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

Say I have my data like this

Doc # | Item #
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:

Doc # | Item #
123     EDS100
123     DDS200
123     DDS500
125     FGD450
125     LDS552
125     DDS200
125     EDD200

I'm sure this is trivial, I have tried the following: SELECT doc#, item# FROM orders WHERE item# like 'DDS200'; < this only returns the rows that meet the criteria, not all the rows of the order as shown above.

Thanks for your help

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.

Doc # | Item #
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:

Doc # | Item #
123     EDS100
123     DDS200
123     DDS500
125     FGD450
125     LDS552
125     DDS200
125     EDD200

I'm sure this is trivial, I have tried the following: SELECT doc#, item# FROM order T0 INNER JOIN items T1 ON T0.doc = T1.doc WHERE T1.item# like 'DDS200'; < this only returns the rows that meet the criteria, not all the rows of the order as shown above.

Thanks for your help

Source Link
Nicolas
Nicolas
  • 41
  • 1
  • 1
  • 4