abcdx Posted March 18, 2009 Share Posted March 18, 2009 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 More sharing options...
kickstart Posted March 18, 2009 Share Posted March 18, 2009 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 Link to comment https://forums.phpfreaks.com/topic/150064-joining-multiple-results-into-one-table/#findComment-788132 Share on other sites More sharing options...
abcdx Posted March 19, 2009 Author Share Posted March 19, 2009 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. Link to comment https://forums.phpfreaks.com/topic/150064-joining-multiple-results-into-one-table/#findComment-788151 Share on other sites More sharing options...
kickstart Posted March 19, 2009 Share Posted March 19, 2009 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 Link to comment https://forums.phpfreaks.com/topic/150064-joining-multiple-results-into-one-table/#findComment-788328 Share on other sites More sharing options...
abcdx Posted March 19, 2009 Author Share Posted March 19, 2009 Thanks a lot Keith. I'll try it out. Link to comment https://forums.phpfreaks.com/topic/150064-joining-multiple-results-into-one-table/#findComment-788382 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.