How to join 3 tables and order by the date of all 3?

Question

I have 3 tables,

albums, videos and stories.

All 3 have a date column and each is associated with a subject_id column.

I want to select all subject_id albums, videos, and stories sorted by date. How can i do this?

Now I have this query:

SELECT albums.*, videos.*, stories.* FROM albums LEFT JOIN videos ON videos.subject_id = albums.subject_id RIGHT JOIN stories ON stories.subject_id = albums.subject_id ORDER BY albums.date 

Will it be a trick or am I missing something? Since I want dates not to be ordered simply by albums, stories could have older enteries and then albums, etc.

Answer 1

If I understood correctly, assuming that you always want to sort in ascending order by the earliest date in the set, you can do:

 ORDER BY LEAST(albums.date, videos.date, stories.date) 

"LEAST" is an Oracle function that returns the minimum (that is, the earliest) value in a set. If you are using a database without the simliar command, you can use the CASE statement:

 ORDER BY CASE WHEN albums.date <= videos.date AND albums.date <= stories.date THEN albums.date WHEN videos.date <= albums.date AND videos.date <= stories.date THEN videos.date ELSE stories.date 

Although I'm not sure if this is what you want. You will have a large row with all the information about the album, video and history on one line. If you need a line for each album, video or story, then you most likely need a UNION, not a JOIN set, although then you will need to make sure that you use the same fields. For example, if each table had a header field that you wanted to see, you can use:

 SELECT * FROM ( SELECT 'Album' as RowType, album_title as title, subject_id, date from albums UNION SELECT 'Video' as RowType, video_title as title, subject_id, date from videos UNION SELECT 'Story' as RowType, story_title as title, subject_id, date from stories ) ORDER BY subject_id, date 

Which would give the sort order that I think you need (or you can take subject_id from ORDER BY if you want). The only thing about UNION is that you have to return the same columns in every basic query, so if you want to get completely different information from albums than from videos, it will be difficult.

Answer 2

Do you want to replace

 ORDER BY albums.date 

with

 ORDER BY albums.date, videos.date, stories.date 

First it will be ordered by album date, and then the date of the video in it and the date of the story. I think you will get what you are looking for.