CODEWITHSUNDEEP
mysqljoinwhere-clause
John Fu
John Fu

Reputation: 35

MySQL Multiple LEFT JOIN with WHERE clause

This is a MySQL codes I wrote to join TWO tables, and it successfully generates a new table I desire:

SELECT 
        *
FROM MarketingDb.ListingProduct ListingProduct
LEFT JOIN ErpDb.ProductListingDetail ProductListingDetail
ON ListingProduct.ProductListingId = ProductListingDetail.ProductId
WHERE ProductListingDetail.ComponentType = "S" 

LIMIT 10;

Since I actually wanna join THREE tables, therefore I add 2 new lines of codes to the above SQL; the WHERE clause above is required b4 I join the 3rd table.

SELECT 
        *
FROM MarketingDb.ListingProduct ListingProduct
LEFT JOIN ErpDb.ProductListingDetail ProductListingDetail
ON ListingProduct.ProductListingId = ProductListingDetail.ProductId
WHERE ProductListingDetail.ComponentType = "S" 


LEFT JOIN ErpDb.Product Product
ON Product.SKU = ProductListingDetail.ProductListingId

LIMIT 10;

But this time it fails, and error prompt said I have SQL syntax error for the 2 new lines of codes. Can someone kindly help me identify my error to fix the problem? Thx!

Upvotes: 0

Views: 295

Answers (1)

nbk
nbk

Reputation: 49375

that because wthe Where clause has ti be after the from clause , and your joins bolng all to the FROM Section of the query

Making a SELECT * can get you into troulbe, so you shiuld soecify which columns you actually need

SELECT 
        *
FROM MarketingDb.ListingProduct ListingProduct
LEFT JOIN ErpDb.ProductListingDetail ProductListingDetail
ON ListingProduct.ProductListingId = ProductListingDetail.ProductId
LEFT JOIN ErpDb.Product Product
ON Product.SKU = ProductListingDetail.ProductListingId
WHERE ProductListingDetail.ComponentType = "S"
LIMIT 10;

Upvotes: 1

Related Questions