Joining multiple results into one table

[abcdx]

Hi guys,

 

I'm a bit new here and I was hoping someone could help me out.

 

This is my problem. I've got 3 tables. A Members table, an Articles table and a Blogs table.

As you can guess, member's can post articles and blogs.

 

Basic table structure:

 

Members (memberid, membername)

Articles (articleid, articletitle, created)

Blogs (blogid, blogtitle, created)

 

What I need is to be able to pull out all articles and blogs posted by a particular member and put the results into one table, ordered by the created field.

So like....

 

memberid  |  articleid  | blogid  | created

1            :      1      : null      : 2009-01-01

1            :        2    : null      : 2009-01-02

1            :      null    :  1        : 2009-01-04

1            :      3      :  null    : 2009-01-05

1            :      null    :  2      : 2009-01-06

 

... thanks.

[kickstart]

Hi

 

Not quite what you have asked for but:-

 

SELECT memberid, articleid AS articleblogid, created from Members a JOIN articles b ON a.memberid = b.memberid

UNION

SELECT memberid, blogid AS articleblogid, created from Members a JOIN Blogs b ON a.memberid = b.memberid

ORDER BY created.

 

However this relies on having a column in both the articles and blogs tables for the member id that created them.

 

It does avoid having a null articleid or blogid column for each row.

 

All the best

 

Keith

 

[abcdx]

Thanks Keith,

 

Sorry, I forgot to show in my table structure. The Articles and Blogs tables both have a memberid field in them, so:

  - Articles (articleid, memberid, articletitle, created)

  - Blogs (blogid, memberid, blogtitle, created)

 

You see, what I'm trying to get is a list of all articles and blogs posted by, say, a particular member. The articles and blogs would be listed in one list/table and would be ordered by the date they were posted (using the created field).

 

.... would your sql statement get me that?

 

Thanks again.

[kickstart]

Hi

 

Yes it would, although you probably want to add a marker to know which one is an article and which one is a blog:-

 

SELECT memberid, articleid AS articleblogid, created, "Article" AS articleorblog from Members a JOIN articles b ON a.memberid = b.memberid

UNION

SELECT memberid, blogid AS articleblogid, created, "Blog" AS articleorblog from Members a JOIN Blogs b ON a.memberid = b.memberid

ORDER BY created

 

All the best

 

Keith

[abcdx]

Thanks a lot Keith.

 

I'll try it out.