Glenn Holland Glenn Holland - 7 months ago 10
SQL Question

How to get all rows from a database where foreign key is equal to the result of a query?

So I have a table of "Articles" that has a foreign key of "category_id".

Articles

| id | title | pub_date | category_id |
-----------------------------------
| 0 | abc | 23423443 | 1 |
| 1 | def | 23423423 | 2 |
| 2 | ghi | 24234234 | 1 |
| 3 | jkl | 23423424 | 3 |
| 4 | mop | 23432435 | 2 |


Categories

| id | title |
----------------
| 1 | News |
| 2 | Feature |
| 3 | Review |


I have the title of a category.

I would like to, in one query, ascertain the id of said category and use it to return articles where the category_id = id and publish date is less than the current date time.

Is this possible?

I am using Postgres but I am looking at this from an academic standpoint so answers in any SQL dialect would be fine as I am happy to do the translation myself for the education.

Answer

it's just a simple join try

SELECT A.id,A.title,A.pub_date,A.category_id,
       C.title
FROM Articles A
INNER JOIN Categories C
ON A.category_id = C.id
WHERE pub_date < NOW()