By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,932 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,932 IT Pros & Developers. It's quick & easy.

select unique and return related table

Microblitz
P: 40
This Code selects a unique entry of the Manufacturing Order.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @suffix NVARCHAR(8)
  2. DECLARE @DateStart NVARCHAR(12)
  3. DECLARE @DateEnd NVARCHAR(12)
  4.  
  5. SET @suffix = '/M'
  6. SET @DateStart = '01/05/2013'
  7. SET @DateEnd = '08/05/2013'
  8.  
  9. SELECT a.*  from  ManufacturingOrders a 
  10. INNER JOIN
  11. (SELECT DISTINCT  ManufacturingOrderId FROM ManufacturingOrders) AS b
  12. ON a.ManufacturingOrderId = b.ManufacturingOrderId
  13. ORDER BY ManufacturingOrderId
  14.  
I need to use that to select related information from this query.

Expand|Select|Wrap|Line Numbers
  1. SELECT     ManufacturingOrders.QuantityMade AS Quantity_Made, Products.ProductId, Classifications.ClassificationId, Products.ProductDescription, 
  2.                       ManufacturingOrders.QuantityOutstanding AS Outstanding, ManufacturingOrders.DueDate, ManufacturingOrders.ReleaseDate, 
  3.                       ManufacturingOrders.ManufacturingOrderId, Inventory.EffectiveDate, Inventory.CreatedDate, Inventory.LotNumber, Inventory.WorkInProgress, 
  4.                       Inventory.QuantityFilled
  5.  
  6. FROM       ManufacturingOrders INNER JOIN
  7.                       Products ON ManufacturingOrders.Product = Products.Product INNER JOIN
  8.                       Classifications ON Products.Classification = Classifications.Classification INNER JOIN
  9.                       Inventory ON ManufacturingOrders.ManufacturingOrder = Inventory.ManufacturingOrder AND Products.Product = Inventory.Product
  10. WHERE     (RIGHT(Products.ProductId, 2) = UPPER(@suffix)) AND (dbo.wfn_GetSimpleDate(Inventory.CreatedDate) BETWEEN @DateStart AND @DateEnd)
  11. ORDER BY Classifications.ClassificationId, ManufacturingOrders.ReleaseDate DESC
Can anyone suggest how I can tack these two pieces of code together without dropping data. Essentially I need to get the data from the second query ONLY if its the first occurance of the Manufacturing order so I can return the correct requested order amount as there are multiple instances of the Manufacturing order table (I know somebody (not me) messed up!)
May 8 '13 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,430
I'm not sure I understand your question. Some sample data and results would help.
May 8 '13 #2

Microblitz
P: 40
I rewrote this and solved it.
May 17 '13 #3

Rabbit
Expert Mod 10K+
P: 12,430
Glad you got it fixed. Can you post your solution in case someone else runs into the same situation?
May 17 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.