Jump to content

Joining multiple results into one table


abcdx

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/150064-joining-multiple-results-into-one-table/
Share on other sites

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

 

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.

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.