sql select max note for a row and join

[Shashi Sadasivan]

hi,
i have an issue with joining some table.
i have 3 tables.
product - (productID, total)
items - (productID, itemNo, total)
note - (noteID, productID, note)

each product has one or more items
each product has zero or more notes to it to the note

i need to show each product along with the last note put on it (there might be zero or more notes per product)

what i want to select is productID,total,item_no,note
the note should be the latest note for that productID
managed to get those rows which dont have a note. but cant work the other way around!

Thanks
-shashi

[ak1dnar]

hi,
i have an issue with joining some table.
i have 3 tables.
product - (productID, total)
items - (productID, itemNo, total)
note - (noteID, productID, note)

each product has one or more items
each product has zero or more notes to it to the note

i need to show each product along with the last note put on it (there might be zero or more notes per product)

what i want to select is productID,total,item_no,note
the note should be the latest note for that productID
managed to get those rows which dont have a note. but cant work the other way around!

Thanks
-shashi

Hi Shashi,

When you joining note table with others two table you can Select the Maximum noteid for the specific product.

Do you think this is the one you looking for. Post back if not.

select items.productID,items.total,items.itemno,note.note from note
Left join items on items.productID=note.productID 
where note.productid=1001 and note.noteid=(select max(noteid) from note where note.productid=1001)

[Shashi Sadasivan]

Hi Shashi,

When you joining note table with others two table you can Select the Maximum noteid for the specific product.

Do you think this is the one you looking for. Post back if not.

select items.productID,items.total,items.itemno,note.note from note
Left join items on items.productID=note.productID 
where note.productid=1001 and note.noteid=(select max(noteid) from note where note.productid=1001)

Not really....
1. i have to list it for all products.
2. if there is no note for that product the product should still be shown
in the above case, i would need to programatically run this query for each product
and if there is a product without any note, it would not be shown
thanks

[Shashi Sadasivan]

Hi Shashi,

When you joining note table with others two table you can Select the Maximum noteid for the specific product.

Do you think this is the one you looking for. Post back if not.

select items.productID,items.total,items.itemno,note.note from note
Left join items on items.productID=note.productID 
where note.productid=1001 and note.noteid=(select max(noteid) from note where note.productid=1001)

Sorry about the last reply...
I did not realize about the left join and took it for granted.
Yes it does work great now....thanks mate