CODEWITHSUNDEEP
phpmysqlsqldatabase
Admir Husić
Admir Husić

Reputation: 61

Query one table, to get name from another table

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.

Upvotes: 0

Views: 663

Answers (2)

Jethan
Jethan

Reputation: 71

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';

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

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

Upvotes: 1

Related Questions