Joining the result of two queries mysql

Without actually giving all the details of my query: Is there a way of joing the results of two separate queries on different tables? for example if i had a table

result_table1

id name address
1  Joe  5 Street
2  Max  6 road
3  Jon  4 place

result_table2

id  occupation 
1    Student
2    Lawer
3    Carpenter

New table

id name address   occupation
1  Joe  5 Street  Student
2  Max  6 road    Lawer
3  Jon  4 place   Carpenter

If the first two tables were just tables I would use a left join, but the first two tables are actually made from other select statements which use counts and sums and are grouped. When I try to combine the two select statements 开发者_如何学Cthe grouping gives me unexpected results.

Real world Example

I understand that this must be difficult to solve when I don't put up an actual example so with that in mind

The first Query

SELECT 

bbs.id AS bb_id,
count(bb_replies.bbs_id) AS num_replies,
bb_locations.title AS location,
bb_locations.description AS location_description,
bb_categories.title AS category,
bb_categories.description AS category_description,
users.first_name AS first_name,
users.last_name as last_name,
users.id AS user_id,
bbs.title AS post_title,
bbs.content,
bbs.created_date,
bbs.rank 

FROM `bbs` 

LEFT JOIN bb_locations ON bbs.bb_locations_id = bb_locations.id 
LEFT JOIN bb_categories ON bbs.bb_categories_id = bb_categories.id 
LEFT JOIN users ON bbs.users_id = users.id 
LEFT JOIN bb_replies ON bbs.id = bb_replies.bbs_id 

GROUP BY bb_id,location,location_description,category,category_description,first_name,last_name,user_id,post_title,content,created_date,rank

The second query

SELECT bbs_id,
       sum(CASE WHEN user_id = 2 THEN like_dislike END) AS thisUsersRating,
       SUM(CASE WHEN like_dislike = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN like_dislike = 0 THEN 1 ELSE 0 END) AS dislikes
FROM bb_ratings
GROUP BY bbs_id

How do I join these two queries in such a way that the grouping does not produce strange results?

The database

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 26, 2010 at 05:28 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bulletin_board_application`
--

-- --------------------------------------------------------

--
-- Table structure for table `bbs`
--

CREATE TABLE IF NOT EXISTS `bbs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bb_locations_id` int(11) NOT NULL,
  `bb_categories_id` int(11) NOT NULL,
  `users_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_date` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `bbs`
--

INSERT INTO `bbs` (`id`, `bb_locations_id`, `bb_categories_id`, `users_id`, `title`, `content`, `created_date`, `rank`) VALUES
(1, 1, 2, 1, 'Bulletin post 2', 'This is the content of my second bulletin post', 1290792252, 2),
(2, 1, 2, 1, 'Bulletin post 1', 'The content of my first bulletin post', 1290792124, 1),
(3, 1, 2, 2, 'Bulletin Post 3', 'This is the content of bulletin post 3\r\n', 1290792555, 3),
(4, 2, 1, 1, 'bulletin post 4', 'This is my fourth bulletin post', 1290800287, 4);

-- --------------------------------------------------------

--
-- Table structure for table `bb_categories`
--

CREATE TABLE IF NOT EXISTS `bb_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `order` varchar(255) NOT NULL,
  `admin` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bb_categories`
--

INSERT INTO `bb_categories` (`id`, `title`, `description`, `order`, `admin`) VALUES
(1, 'Free Stuff', 'Free stuff in the office', '1', 2),
(2, 'Office Anouncements', 'This is an anouncement for the office', '2', 3);

-- --------------------------------------------------------

--
-- Table structure for table `bb_locations`
--

CREATE TABLE IF NOT EXISTS `bb_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bb_locations`
--

INSERT INTO `bb_locations` (`id`, `title`, `description`, `address`, `order`) VALUES
(1, 'Washington DC', 'Washington DC chinatown office', 'H street chinatown 20001', 0),
(2, 'San Francisco', 'San Francisco office', 'g street in sf 20395', 1);

-- --------------------------------------------------------

--
-- Table structure for table `bb_ratings`
--

CREATE TABLE IF NOT EXISTS `bb_ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bbs_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `like_dislike` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `bb_ratings`
--

INSERT INTO `bb_ratings` (`id`, `bbs_id`, `user_id`, `like_dislike`) VALUES
(15, 4, 2, 0),
(14, 4, 1, 0),
(13, 3, 1, 0),
(12, 2, 1, 1),
(11, 1, 2, 0),
(10, 1, 1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `bb_replies`
--

CREATE TABLE IF NOT EXISTS `bb_replies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_id` int(11) NOT NULL,
  `bbs_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_date` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `bb_replies`
--

INSERT INTO `bb_replies` (`id`, `users_id`, `bbs_id`, `content`, `created_date`, `rank`) VALUES
(1, 1, 1, 'This is a response to the bulletin post with an id of 1', 1290799543, 1),
(2, 1, 1, 'This is a second response to the post with an id of 1', 1290799778, 1),
(3, 1, 2, 'This is a response to the bulletin post with an id of 2\r\n', 1290799827, 1),
(4, 1, 2, 'This is a second response to the bulletin post with an id of 2\r\n', 1290799858, 1),
(5, 1, 3, 'Reply to bulletin with an id of 3', 1290799924, 1),
(6, 1, 3, 'Another reply to the post which has an id of 3\r\n', 1290799962, 1),
(7, 1, 1, 'This is a third reply for the bulletin post with an id of 1\r\n', 1290801268, 1),
(8, 1, 2, '3rd reply for bulletin with id = 2', 1290801445, 2),
(9, 1, 2, '3rd reply for bulletin with id = 2', 1290808030, 3);

-- --------------------------------------------------------

--
-- Table structure for table `bb_reply_ratings`
--

CREATE TABLE IF NOT EXISTS `bb_reply_ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bb_replies_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `like_dislike` tinyint(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `bb_reply_ratings`
--


-- --------------------------------------------------------

--
-- Table structure for table `bb_sort_bys`
--

CREATE TABLE IF NOT EXISTS `bb_sort_bys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bb_sort_bys`
--

INSERT INTO `bb_sort_bys` (`id`, `title`, `description`) VALUES
(1, 'Newest', 'Posts are sorted by their creation date'),
(2, 'Popular', 'Posts are sorted by their rank');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(10) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `permission` int(1) NOT NULL,
  `bb_sort_bys_id` varchar(10) NOT NULL,
  `bb_locations_csv` varchar(255) NOT NULL,
  `bb_categories_csv` varchar(255) NOT NULL,
  `total_bulletins` int(5) NOT NULL,
  `bulletins_per_page` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `user_name`, `first_name`, `last_name`, `permission`, `bb_sort_bys_id`, `bb_locations_csv`, `bb_categories_csv`, `total_bulletins`, `bulletins_per_page`) VALUES
(1, 'ahughesg', 'Andrew', 'Hughes-Games', 1, '1', '1,2', '1,2', 20, 5),
(2, 'joeblack', 'Joe', 'Black', 3, '2', '1,2', '1,2', 20, 2),
(3, 'jondoe', 'Jon', 'Doe', 3, '1', '1,2', '1,2', 20, 4);

Completing @Microgen's answer... Since you already have your two first select working as you want, you can keep those results in temporary tables:

create temporary table tmp1 as <your first select>;
alter table tmp1 add <some index to accelerate your join later>;
create temporary table tmp2 as <your second select>;
alter table tmp2 add <some index to accelerate your join later>;

Then, you could apply a simple join to get your final result:

select tmp1.id, tmp1.name, tmp1.address, tmp2.occupation
    from tmp1 inner join tmp2 using (id)
    order by tmp1.id;

Another way to do it is to use VIEW, but since the indexing is not the best thing they have, I would avoid them, specially when your first two select are as complex.

use temporary tables for the first two tables then use them to do the join but i guess you may have problem because there is no common attribute to join upon unless you wish to use the ids of the tables if they happen to match

None of this (temp tables, joining two result sets) is necessary.

You are much better off writing queries directly from your source tables. Not only for performance reasons (1 query is obviously better than 2 joined queries plus one), but for your understanding and progress.

Begin with something like

SELECT tablea.id,tablea.name,tablea.address,tableb.occupation

and put something like this in the WHERE clause:

WHERE tablea.id = tableb.id

I don't think that you need a left join here.

I strongly suggest posting your SQL so a proper solution can be achieved. That is to say you should do this with MySQL instead of PHP.

However, you will need to loop over your first set of records and build an array. Then loop over the second, appending it to the first set where the id matches.