CODEWITHSUNDEEP
mysql
Prashant Bhatt
Prashant Bhatt

Reputation: 517

#1054 - Unknown column 'purchasedetail.itemId' in 'on clause'

I have an error in purchasedetail table when I am trying to fetch this record. This is my select query:

SELECT itemstock.itemId
  FROM  itemstock
  JOIN item ON item.itemId = purchasedetail.itemId
  JOIN purchasemaster ON purchasemaster.purchaseMasterId = purchasedetail.purchaseMasterId
  JOIN purchasedetail ON purchasedetail.itemId = item.itemId
  JOIN party ON party.partyId = purchasemaster.partyId
WHERE
  purchasemaster.partyId = ".$_REQUEST['partyId']."
  AND itemstock.quantity > 0
GROUP BY itemName,itemCode

This is the error:

#1054 - Unknown column 'purchasedetail.itemId' in 'on clause'

Upvotes: 1

Views: 146

Answers (1)

naomi
naomi

Reputation: 1984

When joining tables, the columns you join "on" have to belong to those tables. You are trying to join on columns from other tables that you haven't mentioned yet. Try this:

SELECT itemstock.itemId 
FROM itemstock 
JOIN item ON item.itemId = itemstock.itemId 
JOIN purchasedetail ON purchasedetail.itemId = item.itemId 
JOIN purchasemaster ON purchasemaster.purchaseMasterId = purchasedetail.purchaseMasterId 
JOIN party ON party.partyId = purchasemaster.partyId 
WHERE purchasemaster.partyId = 5 AND itemstock.quantity > 0 GROUP BY itemName,itemCode

Upvotes: 1

Related Questions