Jump to content

Best method - join or individual queries?


genista
Go to solution Solved by Barand,

Recommended Posts

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.