Selecting newest related record in join 1

[pkahlo]

I'm having trouble creating a query that will select the newest record in a related record. I have a table of items that have related records in the table "StockTake". StockTake contains 3 fields:

ItemID
StockTakeDate
Quantity

Basically, I want to select the record in StockTake that has the most recent StockTakeDate for each Item. The below query does this, but I also want this query to select all items that have NO records in StockTake.

This is what I have so far:

SELECT Items.*, StockTake.StockTakeDate, StockTake.Quantity
FROM Items LEFT JOIN StockTake ON Items.ItemID = StockTake.ItemID
WHERE (StockTake.StockTakeDate)=(SELECT
MAX(StockTake.StockTakeDate)
FROM
StockTake
WHERE
Items.ItemID = StockTake.ItemID)


How can I make this also select items with no records in StockTake?

Thanks!

 

[Nowell]

Hi,

try this:

WHERE ((StockTake.StockTakeDate)IN(SELECT
MAX(StockTake.StockTakeDate)
FROM
StockTake
WHERE
Items.ItemID = StockTake.ItemID)
OR

((StockTake.StockTakeDate)NOT IN(SELECT
MAX(StockTake.StockTakeDate)
FROM
StockTake
WHERE
Items.ItemID = StockTake.ItemID)

 

[nicsin]

I believe if you add

or StockTake.StockTakeDate is null

at the where clause you will get these records.

 

[nicsin]

Nowell,

don't they cancel one another?

 

[pkahlo]

Thanks nicsin! I thought I had tried something like that already and it wasn't working.

 

[nicsin]

Happy to help pkahlo!

 

[Nowell]

Yeah you are right actually,

sorry chaps its Friday and I haven't plugged my brain in yet.