Best method - join or individual queries?

[genista]

All,

 

I am trying to join two tables, one contains information about an author, the other their posts. I want to retrieve the latest posts from the posts table. 

 

 

As an example:

 

Table: 'Detail'

 

content_id, title, approved

 

1,               fred,        y

2,               tommy,    y

 

 

Table: 'posts'

 

content_id, item_title, item_date

 

1,                blah 1,          19/02/2014

2,                blah 2,          19/02/2014

1,                blah 3,          17/02/2014

2,                blah 4,          17/02/2014

 

I would expect to see from my query below:

 

content_id,   title,       item_date

1,                blah 1,     19/02/2014

2,                blah 2,     19/02/2014

 

 

 

This query does not retrieve any results: 

SELECT t1.content_id, t1.title, t1.source_image, t1.url, t1.approved
FROM detail AS t1
INNER JOIN (

SELECT content_id, item_id, item_title, max( item_date ) 
FROM posts GROUP BY content_id
) AS t2 ON t1.content_id = t2.content_id

I have split them out into two queries for testing:

SELECT content_id, title, source_image, url, approved FROM content_detail 

This retrieves all data from that table as expected.

 

The following query just returns the first row instead of all of them:

SELECT content_id, item_id, item_title, max( item_date ) 
FROM posts

I want to avoid splitting these queries into two, ie get the author detail then for each returning their top Post so any help on how to do this with just one would be great.

 

Thanks,

 

 

G

 

[Barand]

First thing to do if you want to do date comparisons is use the correct date format yyyy-mm-dd. You cannot correctly compare other formats.

[gristoi]

you have no need to use a sub select:

SELECT t1.*, p.*
FROM detail t1
INNER JOIN posts p USING(content_id)

[gristoi]

remember aswell using an inner join means records need to be in table on both sides, or no resuts

[genista]

Thanks all,

 

Barand: Apologies, the date format was just for putting over what I am trying to do, the formats are ok in the database.

 

Gristoi: I will remove the second select, as for the data being on both sides, the content_id is where I am making the join. Other than that the data is different so I am guessing I need two queries - one to pull the author, then once I have that find the latest post for each author?

[Barand]

try

SELECT i.content_id, i.item_title, i.item_date 
FROM items i
INNER JOIN
    (
    SELECT content_id, MAX(item_date) as item_date
    FROM items
    GROUP BY content_id
    ) as max USING (content_id, item_date)

[genista]

Thanks for that, I think I have it solved from this.

 

G