help for a JOIN query

Question

Hi, i've this 3 following table

table ads
...
categoryID
subcategoryID


table category
categoryID
description

table subcategory
subCategoryID
description

How can i JOIN this 3 table to have all field from ADS and category description and also subcategory description...

(when i use sql server, i can "design" the relations, but now, i'm using mysql and i haven't a graphical environment to do it.. and i'm not good to write the sql)

THanks in advance

Answer 1

SELECT 
    ads.*,
    c.description as categoryDescription,
    sc.description as subcategoryDescription,
FROM
    ads 
    INNER JOIN category c ON c.categoryID = ads.categoryID
    INNER JOIN subcategory sc ON sc.subcategoryID = ads.subcategoryID

Note: it is better practice to use an explicit column list rather than '*'

Answer 2

SELECT ads.categoryID,  
       ads.subcategoryID, 
       category.description AS category,  
       subcategory.description AS subcategory
FROM ads 
    JOIN category ON ads.categoryID = category.categoryID
    JOIN subcategory ON ads.subCategoryID = subcategory.subCategoryID

I have avoided the use of ads.* on principle!

Answer 3

SELECT
  ads.*,
  category.description AS categorydescription,
  subcategory.description AS subcategorydescription
FROM ads
INNER JOIN category ON ads.categoryID = category.categoryID
LEFT JOIN subcategory ON ads.subcategoryID = subcategory.ID

I've left the subcategory in as a left join in case your 'ads' records only exist in a top-level category and not in a subcategory. If your 'ads' records are always in a subcategory, then switch this to an inner join.

This assumes also that your ads are only present in a single category and subcategory

Answer 4

SELECT a.`categoryID`,a.`subcategoryID`,c.`description` AS catDesc,s.`description` AS subCatDesc FROM `ADS` a 
INNER JOIN `category` c ON a.`categoryID`=c.`categoryID` 
INNER JOIN `subcategory` s ON a.`subcategoryID`=s.`subCategoryID`

That SQL should do it, you can access the category tables description by catDesc, the subcategorys description by subCatDesc, the categoryID by categoryID and the subcategoryID by subcategoryID