Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    20

    Unanswered: Join/Union Two Tables (Articles and Videos)

    I've got two tables, "videos" and "articles". I want to list the most recently added ones on my frontpage, for example. I want to join the two tables together, and then display the most recent ones using ORDER BY creation_date DESC from the new combined list.

    "videos":
    video_id, youtube_id, video_title, video_intro, video_content, video_picture, author_id, creation_date, last_modified_date, frontpage, video_path

    "articles":
    article_id, article_title, article_intro, article_content, article_picture, author_id, creation_date, last_modified_date, frontpage, article_path

    I need to join these two tables together so that for example, article_title and video_title become "title".

    Is there any way to do this, or must article_title and video_title (for example) be named the same in the table?

    Thanks

    P.S.
    I've tried the following, but i know its not even close to right

    SELECT article_id, article_title as title, article_intro, article_content, article_picture, a.author_id, v.author_id, date_format(a.creation_date, '%M %D, %Y') AS formatted_creation_date, date_format(v.creation_date, '%M %D, %Y') AS formatted_creation_date, article_path, video_title as title, video_intro, video_content, video_picture, video_path, a.creation_date as create_date, v.creation_date as create_date
    FROM articles as a, videos as v
    ORDER BY creation_date DESC

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    select article_title as title
            ,other article columns...
            ,creation_date
       from articles
    union
    select video_title as title
            ,other video columns...
            ,creation_date
       from videos
    order by creation_date
    As an aside, since the tables are nearly identical, why do you have separate tables? You could have included a type column which would define video/article. For that matter, you could include types such as (e)book, (e)magazine, picture, etc...
    Dave

  3. #3
    Join Date
    Dec 2009
    Posts
    20
    I will try out that select statement, thanks!

    Quote Originally Posted by dav1mo View Post
    As an aside, since the tables are nearly identical, why do you have separate tables? You could have included a type column which would define video/article. For that matter, you could include types such as (e)book, (e)magazine, picture, etc...
    Dave
    There is a one key difference between the tables: videos has a "youtube_id" column, which articles would never fill. Would it still be worth it to combine the tables, even though youtube_id would be blank for hundreds of rows?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    If were my system I would not care.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •