Ok, I get it. But there's a catch

PartNo is in Parts. The remain data is in PartHistory:

CREATE TABLE Parts
	(PartId		INT IDENTITY PRIMARY KEY,
	 PartNo		VARCHAR(30)	NOT NULL,
	 Description	TEXT		NOT NULL)

CREATE TABLE PartHistory
	(HistoryId	INT IDENTITY PRIMARY KEY,
	 PartId		INT		NOT NULL,
	 UserId		INT		NOT NULL,
	 LocationId	INT		NULL,
	 SerialNo	VARCHAR(30)	NULL,
	 Quantity	INT		NOT NULL,
	 DropShip	TINYINT		NULL,
	 Printed	TINYINT		NULL,
	 ActionDate	DATETIME	NULL DEFAULT GETDATE(),	
	 Sequence	INT		NOT NULL,
	 Comments	TEXT		NULL)

So, given this, how do I join these tables in this query you gave me?


>Try

>-- or 
>SELECT * FROM mytable mt1
>  WHERE NOT EXISTS (SELECT 1 FROM mytable mt2 
>                  WHERE mt1.PartNo = mt2.PartNo AND mt1.SerialNo = mt2.SerialNo
>                    AND mt1.Sequence < mt2.Sequence)
>- or
>SELECT * FROM mytable mt1
>  WHERE 1 =  (SELECT COUNT(*)  FROM mytable mt2 
>                  WHERE mt1.PartNo = mt2.PartNo AND mt1.SerialNo = mt2.SerialNo)
>
>- or (may not work because of NULL date)
>SELECT * FROM mytable mt1
>  WHERE NOT EXISTS (SELECT 1 FROM mytable mt2 
>                  WHERE mt1.PartNo = mt2.PartNo AND mt1.SerialNo = mt2.SerialNo
>                    AND mt1.Date <> mt2.Date)
>

>>I have a table that contains shipping and receiving info for parts. The first record is the shipping record. Subsequent records are receipt records:
>>
>>

>>
>>This part has been shipped from location 1, received at location 152, the moved to 
>>locations 565 and 202
>>
>>RecId PartNo	LocationId	SerialNo        Qty		Date	                Sequence
>>-----------------------------------------------------------------------------------
>>5228	 17908	1		HUB5K043NC	1		NULL			1
>>5229	 17908	152		HUB5K043NC	1		2009-01-22 00:00:00.000	2
>>5230	 17908	565		HUB5K043NC	1		2009-02-02 00:00:00.000	3
>>5231	 17908	202		HUB5K043NC	1		2009-02-02 00:00:00.000	4
>>
>>
>>
>>This part has been shipped from location 1, but not yet received
>>
>>RecId PartNo	LocationId	SerialNo	 Qty		Date	                       Sequence
>>-----------------------------------------------------------------------------------
>>5953	16577	1		3BV0GW03	1		2009-04-01 00:00:00.000	1
>>
>>

>>
>>The combination of PartNo and SerialNo make a record unique. How do I pull only records that have not yet been received?