[SOLVED] please how can you do this?

[jonniejoejonson]

I have 2 tables:

 

images_table

imageId : imageFilename : dateCreated

 

videos_table

videoId : videoFilename : dateCreated

 

I want to select the id's of the 10 most recent videos or images.

Therefore i need to somehow join the 2 tables and order them by dateCreated ASC and LIMIT 0,10.

 

any ideas thanks Jon.

[jonniejoejonson]

$sql = "(SELECT a.imageId, a.dateCreated AS orderDate FROM library_images AS a

WHERE a.userId='$userId')

UNION

(SELECT b.videoId, b.dateCreated AS orderDate FROM library_videos AS b

WHERE b.userId='$userId')

ORDER BY orderDate DESC LIMIT 0,10";

[kickstart]

Hi

 

That is pretty much what I was about to put, just with a field to say whether it was an image or video.

 

SELECT *
FROM 
(SELECT 'Image' AS Type, imageId AS Id, imageFileName AS FileName, dateCreated FROM images_table
UNION
SELECT 'Video' AS Type, videoId AS Id, videoFilename AS FileName, dateCreated FROM videos_table) Deriv1
ORDER BY dateCreated DESC
LIMIT 0,10

 

All the best

 

Keith

[jonniejoejonson]

thanks Keith.