quote:

---

Originally posted by bklr

try this too
select vendorproductcode from detail
where productID = 'y' and headerid in (select headerid from header where vendorid = 'x')

select vendorproductcode from detail d
where productID = 'y' and exists  (select * from header where headerid = d.headerid and vendorid = 'x' )

---

quote:

---

Originally posted by tkizer

The JOIN method shown in my first post is the preferred method for what has been described.

---

SELECT TOP 1.*
FROM Header T
INNER JOIN Detail D
ON T.HeaderId = d.HeaderID
WHERE T.VendorID = 4 AND D.ProductID = 2453
Order by T.Purcdate desc

quote:

---

Originally posted by smh

I want to thank everyone for these samples. when I tried them and received some errors, I realized that maybe I did not properly explain.
There may be more than one vendorproductcode for a vendorID and a productID combination, so I want to take the most recent one.
here might be some data in both tables. The queries work correctly until I put in the order by clause: order by tblProductPurchaseHeader.purchasedate desc

I have put some sample data in the next post.
If the query was on vendorid = 4 and product ID = 2453, only the first two would fit that criteria. Then by ordering by purchdate and taking the top 1, only the first record would appear in the results.

The question is where to put the order by. In the top query when I put in the order by clause I get a "multi-part identifier could not be bound error msg"

The other queries gave similar errors.

---

quote:

---

Originally posted by smh

I should say that the recent one from yak knowledge works and I changed the order by to t.purchasedate desc and that works. Here is what I did with the others. I am putting the order by clause in the wrong spot:


SELECT d.vendorproductcode
FROM tblProductPurchaseHeader h
INNER JOIN tblProductPurchasedetail d
ON h.ProductPurchaseID = d.ProductPurchaseID
WHERE h.vendorID = 4 AND d.productID = 2413
order by tblProductPurchaseHeader.purchasedate desc

select vendorproductcode from tblProductPurchasedetail
where productID = 2413 and ProductPurchaseID in (select ProductPurchaseID from tblProductPurchaseHeader
where vendorid = 4) order by tblProductPurchaseHeader.purchasedate desc

and

select vendorproductcode from tblProductPurchasedetail d
where productID = 2413 and exists (select * from tblProductPurchaseHeader
where vendorid = 4) order by tblProductPurchaseHeader.purchasedate desc

Here is the one that works:


SELECT TOP 1 vendorproductcode (this is different from the first sample which instead does d.vendorproductcode instead of just vendorproductcode)
FROM tblProductPurchaseHeader T
INNER JOIN tblProductPurchasedetail D
ON T.ProductPurchaseID = d.ProductPurchaseID
WHERE T.VendorID = 4 AND D.ProductID = 2413
Order by T.Purchasedate desc

---

quote:

---

Originally posted by smh

I should say that the recent one from yak knowledge works. The others worked fine too but not when I added the order by clause. That is my fault because I did not mention that I needed only one value. Here is what I did with the others. I am putting the order by clause in the wrong spot. Where should I have placed them?

1)-------------------------------------------

SELECT Top 1 vendorproductcode
FROM tblProductPurchaseHeader h
INNER JOIN tblProductPurchasedetail d
ON h.ProductPurchaseID = d.ProductPurchaseID
WHERE h.vendorID = 4 AND d.productID = 2413
order by h.purchasedate desc

2)------------------------------------

select TOP 1 vendorproductcode from tblProductPurchasedetail d
where productID = 2413 and ProductPurchaseID in (select ProductPurchaseID from tblProductPurchaseHeader h
where vendorid = 4) 
order by h.purchasedate desc

3)--------------------------------------------

Select TOP 1 Vendorproductcode from tblProductPurchasedetail d
where productID = 2413 and exists (select * from tblProductPurchaseHeader h
where headerid = d.headerid and vendorid = 4)  
order by h.purchasedate desc

Here is the one that works:


SELECT TOP 1 vendorproductcode (this is different from the first sample which instead does d.vendorproductcode instead of just vendorproductcode)
FROM tblProductPurchaseHeader T
INNER JOIN tblProductPurchasedetail D
ON T.ProductPurchaseID = d.ProductPurchaseID
WHERE T.VendorID = 4 AND D.ProductID = 2413
Order by T.Purchasedate desc

---

quote:

---

Originally posted by sodeep

It occurs when same columnname is used in 2 or more tables. You should use table alias or name for that column to avoid that.

Yes ,ROW_NUMBER() works in SQL 2008 as well

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by sodeep

It occurs when same columnname is used in 2 or more tables. You should use table alias or name for that column to avoid that.

Yes ,ROW_NUMBER() works in SQL 2008 as well

---

Not true. If columns occurs in two or more tables, the error message will be Ambiguos column name as it cant determine which table column we're referring to.
The above error message is because you either dont have the column available in table or you've not included it in the derived table definition.

---