Ordering a mysql query?

Question

I have 2 tables, Pages and LinkPages.
Within Pages i have the fields:

• pageID (the identifier of the page),
• startmemberID (the id of the member that created the page),
• startDate (date the page was created).

In LinkPages I have:

• pageID (to link with the page),
• linkmemberID (member linking with the page),
• joinDate (date member linked with the page).

What sql query would i use to get all information on the pages with a particular id and then order it by the date the page was started.

I got this far:

SELECT * FROM LinkPages WHERE linkmemberID='MEMBERID' LIMIT 5

but obviously i haven't ordered them here, would i need to use a join?

Many Thanks,

Jai

Answer 1

SELECT lp.pageID, lp.linkmemberID, lp.joinDate
FROM LinkPages lp, Pages p
WHERE lp.linkmemberID='MEMBERID' AND lp.pageID = p.pageID
ORDER BY p.startDate DESC
LIMIT 5

Answer 2

You have two options, either you can JOIN or you can use a subquery:

SELECT * FROM LinkPages WHERE linkmemberID='MEMBERID' 
ORDER BY 
   (SELECT startDate FROM Pages WHERE Pages.pageID = LinkPages.PageID) DESC
LIMIT 5

For good measure, here's the join:

-- be sure to use L.* here, otherwise you get all of the columns from 
-- pages as well
SELECT L.* FROM LinkPages L 
  INNER JOIN Pages P ON P.pageID = L.pageID
WHERE linkmemberID='MEMBERID'
ORDER BY P.startDate DESC  LIMIT 5

Answer 3

SELECT lp.* 
FROM LinkPages lp, Pages p 
WHERE lp.pageId = p.pageId
AND lp.linkmemberID='MEMBERID' 
ORDER BY p.startDate
LIMIT 5

sorry - forgot the ORDER BY ...

Answer 4

Try this:

SELECT * FROM LinkPages
INNER JOIN Pages ON Pages.pageID = LinkPages.pageID
WHERE linkmemberID='MEMBERID'
ORDER BY startDate DESC
LIMIT 5