CODEWITHSUNDEEP
sql
useyourillusiontoo
useyourillusiontoo

Reputation: 1367

SQL Join over 3 tables

I'm trying to pull data from two tables based on another.

So.. I've these tables:

 **tblCategory**
 CategoryID, CategoryName

 **tblProdCat**
 prodCatID, productID, categoryID

 **tblProducts**
 productID, description, price

I need to join the tables and locate information from tblProdCat and tblProducts with only the CategoryName available to me.

   SELECT tblProducts.name, 
                       tblProducts.description, 
                       tblProducts.price, 
                       tblProducts.qtyInStock, 
                       tblProducts.image,
                       tblProducts.colour,
                       tblProducts.size,
                       tblProducts.style,
                       tblProdCat.prodCatID   
                  FROM
                        tblProducts, tblProdCat,tblCategory 
                  WHERE tblCategory.categoryName='Pens' 
                  AND   tblProdCat.categoryID = tblProducts.categoryID

I've tried the above and also tried other joins but an pulling my hair out. I've read tutorials and just do not understand how a join works. Can anybody help? Please..

Upvotes: 0

Views: 441

Answers (3)

Balaji Natarajan
Balaji Natarajan

Reputation: 202

You missed to add one more condition.    
 SELECT tblProducts.name, 
                           tblProducts.description, 
                           tblProducts.price, 
                           tblProducts.qtyInStock, 
                           tblProducts.image,
                           tblProducts.colour,
                           tblProducts.size,
                           tblProducts.style,
                           tblProdCat.prodCatID   
                      FROM
                           tblProducts, tblProdCat,tblCategory 
                      WHERE 
                            tblCategory.categoryName='Pens' 
                      **AND   tblProdCat.categoryID = tblCategory.categoryID
                      AND   tblProducts.productID = tblProdCat.productID**

Upvotes: 0

Taryn
Taryn

Reputation: 247640

You will want to use the following JOIN syntax:

SELECT p.name, 
  p.description, 
  p.price, 
  p.qtyInStock, 
  p.image,
  p.colour,
  p.size,
  p.style,
  pc.prodCatID,
  c.CategoryName
FROM tblProducts p
INNER JOIN tblProdCat pc 
  ON p.productID = pc.productID
INNER JOIN tblCategory c
  ON pc.categoryID = c.categoryID
WHERE c.categoryName='Pens'

I used an INNER JOIN between each table which will return all rows that match between each table.

If you need help learning JOIN syntax, here is a great visual explanation of Joins.

Now, if you wanted to return all Products whether or not it has a category, then you can use a LEFT JOIN:

SELECT p.name, 
  p.description, 
  p.price, 
  p.qtyInStock, 
  p.image,
  p.colour,
  p.size,
  p.style,
  pc.prodCatID,
  c.CategoryName
FROM tblProducts p
LEFT JOIN tblProdCat pc 
  ON p.productID = pc.productID
LEFT JOIN tblCategory c
  ON pc.categoryID = c.categoryID
  AND c.categoryName='Pens'

Upvotes: 1

wax eagle
wax eagle

Reputation: 541

This is the proper syntax for an Inner join which will keep only the records with matching rows category Ids. 

 SELECT tblProducts.name, 
    tblProducts.description, 
    tblProducts.price, 
    tblProducts.qtyInStock, 
    tblProducts.image,
    tblProducts.colour,
    tblProducts.size,
    tblProducts.style,
    tblProdCat.prodCatID 

  FROM tblProducts
  INNER JOIN tblProdCat ON tblProdCat.productId= tblProducts.productId
  INNER JOIN tblCategory ON tblCategory ON tblCategory.categoryId = tblProdCat.categoryId
  WHERE tblCategory.categoryName='Pens'

If some category Ids can be missing and you want those rows then it's prudent to do a LEFT JOIN:

 SELECT tblProducts.name, 
    tblProducts.description, 
    tblProducts.price, 
    tblProducts.qtyInStock, 
    tblProducts.image,
    tblProducts.colour,
    tblProducts.size,
    tblProducts.style,
    tblProdCat.prodCatID 

  FROM tblProducts
  LEFT JOIN tblProdCat ON tblProdCat.productId= tblProducts.productId
  LEFT JOIN tblCategory ON tblCategory ON tblCategory.categoryId = tblProdCat.categoryId
  WHERE tblCategory.categoryName='Pens'

This will return all records from tblProducts regardless of the presence of corresponding records in the other two tables.

Upvotes: 0

Related Questions