asked on

Need an efficient SQL query

I have a table of parts and a table of orders. I need to construct a query that pulls a subset of parts needed for an assembly and the last order placed for each of those parts. These are huge tables so this needs to be an efficient query. I am working in MS SQL 2012

CREATE TABLE [dbo].[Parts_Master](
      [PartNo] [bigint] NOT NULL,
      [Nomenclature] [varchar](255) NOT NULL,
      [bldg] [int] NULL,
      [room] [int] NULL,
      [unit] [int] NULL,
      [row] [int] NULL,
      [shelf] [int] NULL,
      [slot] [int] NULL,
      [IsSubAssy] [bit] NOT NULL,
      [IsAssy] [bit] NOT NULL,
      [Overflow] [int] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Parts_Orders](
      [UID] [bigint] IDENTITY(1,1) NOT NULL,
      [VendorID] [int] NOT NULL,
      [VendorOrderID] [varchar](50) NOT NULL,
      [OrderDate] [datetime] NOT NULL,
      [DetailLine] [int] NOT NULL,
      [PartNo] [bigint] NOT NULL,
      [Quantity] [int] NOT NULL
) ON [PRIMARY]

Jim Horn

Tell us how you would search for 'an assembly', as the only Assembly-looking columns I see are bit values (i.e. Yes or No) and not for a specific assembly.

If that's all it is, then give this a whirl..

SELECT pm.partNo, Max(OrderDate) as last_order_placed
FROM Parts_Master pm
   JOIN Parts_Orders o ON pm.PartNo = o.PartNo
WHERE IsAssy = 1 
GROUP BY pm.partNo
ORDER BY pm.partNo

Open in new window

lippert

ASKER

I don't need the last order for assys or sub assys only for parts. Let me give this a try and see if it works.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

This also means you'll want to define PartNo as the primary key on Parts_Master

ALTER TABLE Parts_Orders
ADD CONSTRAINT PK_Parts_Orders PRIMARY KEY CLUSTERED (PartNo)
GO

Open in new window

lippert

ASKER

Foreign key already exists.

lippert

ASKER

This worked.

Jim Horn

Thanks for the grade, but I'm a little confused as to what worked for you, as you accepted a comment relating to a foreign key, but later stated that 'Foreign key already exists'.