Nested SELECTS, to limit results

[PC Nerd]

hi,

 

Im trying to create a veiw that matches up the most recent "comment" to the article.  Theyve currently called "approvals" because its for the admin to approve/decline etc articles before they are posted on teh site. 

 

Here is my SQL:

 

 

CREATE VIEW `Article_Select` AS  SELECT (

SELECT User_Name

FROM Users

WHERE User_ID = Articles.Author

) AS Author, Articles.Author AS Author_ID, Articles.ID, Articles.Title, Articles.Content, Articles.Date, Articles.Updated, Articles.Type, Article_Approvals.Date AS Approval_Date, Article_Approvals.reason, Article_Approvals.status

FROM `Articles` , `Article_Approvals`

WHERE Articles.ID = Article_Approvals.Article_ID

AND Articles.ID = (

SELECT Article_approvals.Approval_ID

FROM Article_Approvals

WHERE Article_Approvals.Article_ID = ID

ORDER BY Article_Approvals.Date ASC LIMIT 1)

 

 

Im not sure exactly how to explain it... however teh Article_Approvals Table can contain multiple records for a single article..... so that if an admin changes they can simply re-approve/decline it. the status field stores -1, 1, or 2.  -1 is declined perminently ( ie off topic, useless/inappropriate etc), 1 is approved and 2 means that it needs editing for approval.

 

I want the view to show the status adn reason for the most recent approval.... however im slightly confused as to how to do this with nested inserts.

 

Essentially:

 

SELECT <article_fields>, <Most recent Approval Fields> FROM `Articles`, `Article_Approvals`

 

 

Thanks for your help.

 

 

[gerkintrigg]

no, don't nest select queries coz they wont work.

 

you can use brackets () to nest conditions in the same query or use multi-table selects in SQL... you can even get one result from SQL, bung it in an array and then loop through the array to get another SQL select query to work.

[PC Nerd]

so are you saying that i cant do whay i want in the one query (for a view)... or thatt im doign it the wrong way - in which case how would i do it?

 

thanks.

[PC Nerd]

Bump - ill re explain.

 

I have 2 tables.  Articles, and Article_Approvals.

 

I want to join the two tables into one view....

There may be more than one record in Article_Approvals that matches Articles.

Article Approvals has a date.

 

 

I want to match up the two, based on the most recent record in Article Approvals.

 

now to receive a single row its

SELECT <whatever> FROM Article_Approvals ORDER BY Date LIMIT 1 - that will get the most recent....

 

how can i do a similar thing for every row on the other table?

 

Thanks

[fenway]

You need to first use a query to get the most recent (e.g. with MAX(yourDateField) and GROUP BY), and then JOIN this derived table to the parent table.

[PC Nerd]

hmmm - thanks

 

Ive just setup so that a cron runs and creates a secodn table that holds the most recent copy of the approval... and then the view just works on that join.... but ill have a play around with that - thanks.