Query one table, to get name from another table

0
votes

I have 3 tables in my database, Categories,Subcategories and Articles. My Articles table contains columns caled CategoryID and SubcategoryID, which are foreign keys of the table Categories and Subcategories.

My question is, how can I get the name of the Category and Subcategory stored only as ID's in my Articles Table.

I think I need a kind of subquery or join.

Here is what I have: 

SELECT ArticleTitle
      ,CategoryID
      ,SubcategoryID 
  FROM Articles 
 WHERE SubcategoryID = 
        (SELECT SubcategoryName 
           FROM Subcategories 
          WHERE SubcategoryName = 'info'
         )

When I execute this code in mysql, there are no erros, but I receive 0 results. All tables are containing some data.

2
phpmysqlsqldatabase
personal advice: try to format your SQL-statements - have a look at SQL style guide - proper formated SQL statements make trouble shooting & all futher changes easier for you and your fellows – Esteban P.

2 Answers

1
votes

change this:

where SubcategoryID = (select SubcategoryName from Subcategories

to this

where SubcategoryID in (select SubcategoryID from Subcategories

the changes were

  1. the equal sign is now the word in.
  2. the subquery is selecting SubcategoryID instead of SubCategoryName
0
votes

Using Joins :

SELECT a.ArticleTitle AS ArticleTitle, c.CategoryName AS CategoryName,s.SubcategoryName AS SubcategoryName

FROM

Articles a JOIN Categories c on a.CategoryID=c.ID JOIN Subcategories s on a.SubcategoryID=s.ID

WHERE s.SubcategoryName = 'info';