0

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.

Glenn Holland
  • 580
  • 3
  • 19

3 Answers3

1
select * 
    from Articles
  where pub_date <= now() and 
        category_id = (select id from Categories where title="TITLE")
splash58
  • 26,043
  • 3
  • 22
  • 34
  • This helped a lot, I ended up going the JOIN route rather than sub query but thanks for your answer. – Glenn Holland Apr 24 '16 at 21:15
  • ok. i think @BobSalmon answer is better among join variants because the where clause `title = CATEGORY TITLE` whould reduce the temp table and so the time of execution – splash58 Apr 24 '16 at 21:23
  • Whether a sub-query (as in this answer) or join is better, the answer is: it depends (http://stackoverflow.com/questions/2577174/join-vs-sub-query). This answer is a bit old, but I think still relevant. It's worth knowing how to do both, so you can use whichever works best in your situation (which may vary). – Bob Salmon Apr 25 '16 at 06:30
  • @BobSalmoni am not comparing your and my answers. I wrote about two answers with join :) – splash58 Apr 25 '16 at 09:45
1

Is this the kind of thing you're after? I don't know if this is PostgresSQL or not.

select A.id, A.title, A.pub_date, C.id, C.title
from Articles A join Categories C on A.category_id=C.id
where C.title = MY_CATEGORY_TITLE and
a.pub_date < CURRENT_DATE_TIME

I'm ignoring the need for indexes, orders etc.

Bob Salmon
  • 411
  • 4
  • 10
  • This worked perfectly, Tin Tran just got there first. Thank you. – Glenn Holland Apr 24 '16 at 21:14
  • 1
    No problem. If you haven't already, it's probably worth reading up on the different kinds of join (inner, outer, left and right). I was sloppy and didn't specify (assuming it would default to inner). Tin Tran has also used inner - I think that's what you need here - but it's useful to know about the other kinds too. – Bob Salmon Apr 24 '16 at 21:16
  • I have looked into them a little bit before now but I can see now that I have a lot to learn. Just thinking my way through the problem with all the possible join variants is in itself somewhat of a brain game. Might be worth me cloning my DB and having a play with some of them. Thanks again. – Glenn Holland Apr 24 '16 at 22:30
0

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()
  AND C.title = "your title"
Tin Tran
  • 6,194
  • 3
  • 19
  • 34
  • I added the extra WHERE clause for the specific Category title, but this helped me get to the answer I needed thank you. – Glenn Holland Apr 24 '16 at 21:16
  • there i updated my query to show this fact that you're looking for a specific category title. i don't think i heard you mention this in your question. but yeah – Tin Tran Apr 24 '16 at 22:42