title: I can't get this mysql join query to produce the desired result
body: <p><strong>The structure for the three tables.</strong></p>
  
  <p>Booking</p>
  
  <pre><code>CREATE TABLE `booking` (
  `bookingID` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_no` int(11) NOT NULL,
  `client` varchar(32) NOT NULL,
  `operator` varchar(32) NOT NULL,
  `discount` int(11) NOT NULL,
  `total_amount` int(64) NOT NULL,
  `amount_paid` int(32) NOT NULL,
  `balance` int(32) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`bookingID`)
  ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>Jobtitle</p>
  
  <pre><code>CREATE TABLE `jobtitle` (
  `jobtitleID` int(11) NOT NULL AUTO_INCREMENT,
  `jobtitle` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL,
  `amount` varchar(255) NOT NULL,
  `jobtypeID` int(11) NOT NULL,
  `bookingID` int(11) NOT NULL,
  PRIMARY KEY (`jobtitleID`)
  ENGINE=InnoDB AUTO_INCREMENT=463 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>First_graphics_debtors</p>
  
  <pre><code>CREATE TABLE `first_graphics_debtors`
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_no` int(11) NOT NULL,
  `date_paid` date NOT NULL,
  `old_balance` int(32) NOT NULL,
  `debtor_amount_paid` int(32) NOT NULL,
  `new_balance` int(32) NOT NULL,
  PRIMARY KEY (`id`)
  ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p><strong>After inserting few data</strong></p>
  
  <p>Booking</p>
  
  <pre><code>+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
  | bookingID | receipt_no | client   | operator | discount | total_amount | amount_paid | balance | date       |
  +-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
  |       205 |  156872940 | Osaro    | Obi      |       10 |       156380 |      135000 |    5742 | 2012-05-15 |
  |       206 |  227349168 | Amaka    | Stephen  |        4 |        73250 |       70320 |       0 | 2012-05-15 |
  |       207 |  155732278 | Aghahowa | Ibori    |        0 |       116836 |       15000 |  101836 | 2012-05-15 |
  |       208 |  753263343 | Chaka    | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 |
  +-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
  </code></pre>
  
  <p>Jobtitle</p>
  
  <pre><code>+------------+---------------------------+----------+--------+-----------+-----------+
  | jobtitleID | jobtitle                  | quantity | amount | jobtypeID | bookingID |
  +------------+---------------------------+----------+--------+-----------+-----------+
  |        454 | A1 Full Colour            |       10 | 4334   |         1 |       205 |
  |        455 | Complementry Card         |       20 | 5652   |         2 |       205 |
  |        456 | A4 Printout (graphics)B/W |       25 | 2930   |         4 |       206 |
  |        457 | Scan                      |        2 | 4334   |         5 |       207 |
  |        458 | A4 Full Colour            |      199 | 500    |         3 |       207 |
  |        459 | ID Card                   |        2 | 4334   |         2 |       207 |
  |        460 | A3 Full Colour            |       10 | 4334   |         3 |       208 |
  |        461 | Flex Banner               |       20 | 2930   |         2 |       208 |
  |        462 | A2 Single Colour          |      199 | 650    |         1 |       208 |
  +------------+---------------------------+----------+--------+-----------+-----------+
  </code></pre>
  
  <p>First_graphics_debtors</p>
  
  <pre><code>+----+------------+------------+-------------+--------------------+-------------+
  | id | receipt_no | date_paid  | old_balance | debtor_amount_paid | new_balance |
  +----+------------+------------+-------------+--------------------+-------------+
  |  7 |  156872940 | 2012-05-15 |        5742 |               5000 |         742 |
  |  8 |  156872940 | 2012-05-15 |        5742 |               5742 |           0 |
  |  9 |  753263343 | 2012-05-15 |      152161 |             152161 |           0 |
  | 13 |  753263343 | 2012-05-15 |      152161 |              14524 |      137637 |
  | 14 |  753263343 | 2012-05-15 |      152161 |               2000 |      150161 |
  | 15 |  753263343 | 2012-05-15 |      152161 |               1000 |      151161 |
  +----+------------+------------+-------------+--------------------+-------------+
  </code></pre>
  
  <p><strong>When I run this query:</strong></p>
  
  <pre><code>SELECT `booking`.`receipt_no`, `client`, `operator`, `discount`, `total_amount`,
         `amount_paid`, `balance`, `date`, `jobtitle`, `quantity`, `amount`,
         `date_paid`, `old_balance`, `debtor_amount_paid`, `new_balance`
    FROM (`booking`)
    JOIN `jobtitle` ON `jobtitle`.`bookingID` = `booking`.`bookingID`
    JOIN `first_graphics_debtors`
      ON `first_graphics_debtors`.`receipt_no` = `booking`.`receipt_no`
   WHERE `booking`.`receipt_no` =  '753263343'
     AND `first_graphics_debtors`.`receipt_no` =  '753263343'
   GROUP BY `jobtitle`.`quantity`
  </code></pre>
  
  <p><strong>I get this output:</strong></p>
  
  <pre><code>+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
  | receipt_no | client | operator | discount | total_amount | amount_paid | balance | date       | jobtitle         | quantity | amount | date_paid  | old_balance | debtor_amount_paid | new_balance |
  +------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
  |  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A3 Full Colour   |       10 | 4334   | 2012-05-15 |      152161 |             152161 |           0 |
  |  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | Flex Banner      |       20 | 2930   | 2012-05-15 |      152161 |             152161 |           0 |
  |  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A2 Single Colour |      199 | 650    | 2012-05-15 |      152161 |             152161 |           0 |
  +------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
  </code></pre>
  
  <p>Data on the columns below are repeated three times, instead of getting data from the four rows relating to the receipt_no</p>
  
  <pre><code>date_paid, old_balance, debtor_amount_paid, new_balance
  </code></pre>
  
  <p><strong>Expected result</strong></p>
  
  <pre><code>+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
  | receipt_no | client | operator | discount | total_amount | amount_paid | balance | date       | jobtitle         | quantity | amount | date_paid  | old_balance | debtor_amount_paid | new_balance |
  +------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
  |  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A3 Full Colour   |       10 | 4334   | 2012-05-15 |      152161 |             152161 |           0 |
  |  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | Flex Banner      |       20 | 2930   | 2012-05-15 |      152161 |             14524  |     137637  |
  |  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A2 Single Colour |      199 | 650    | 2012-05-15 |      152161 |             2000   |      150161 |
  +------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
  </code></pre>
  
  <p>Though,the expected output not complete, but maybe you can get the picture from the data on <code>debtor_amount_paid</code> and <code>new_balance</code></p>