Help with complex mysql query

I have an existing mysql query that I need to add to and I'm not sure how to go about it.

Here is my current sql query.

SELECT tbl_brokerage_names.brokerage_id, tbl_brokerage_names.short_name,
    b.indication, b.max_indication
  FROM tbl_brokerage_names 
  LEFT JOIN (
    SELECT * FROM tbl_recommendation_brokerages
    WHERE recommendation_id = {$_GET['id']}
  ) b ON (tbl_brokerage_names.brokerage_id = b.brokerage_id) 
ORDER BY tbl_brokerage_names.short_name ASC

Here is the query that I need to work into the previous query.

 SELECT * , COUNT( * )
   FROM tbl_st开发者_如何转开发reetaccounts
   JOIN tbl_brokerage_names
 WHERE tbl_brokerage_names.brokerage_id = tbl_streetaccounts.brokerage_id

Basically I need to return a count, so I need to combine these two queries.

You should run these as two separate queries.

The COUNT(*) query will return a single row, so there's no way to "combine" it with the first query while preserving the multi-row result of the first query.

Also, when you SELECT *, COUNT(*) you will get columns from some arbitrary row.

By the way, you have a glaring SQL injection vulnerability. Don't interpolate $_GET parameters directly in your SQL query. Instead, coerce it to an integer:

<?php
$id = (int) $_GET['id'];
$sql = "SELECT ... WHERE recommendation_id = {$id}";

Like @Bill said, you cannot get the count in every row without really weird syntax, but you can get an overall count using GROUP BY ... WITH ROLLUP.

e.g.:

<?php
$id = mysql_real_escape_string($_GET['id']); //works with anything, not just numbers
$query = "
  SELECT tbl_brokerage_names.brokerage_id
         , tbl_brokerage_names.short_name
         , b.indication
         , b.max_indication
         , count(*) as rowcount 
    FROM tbl_brokerage_names 
    LEFT JOIN (
      SELECT * FROM tbl_recommendation_brokerages
      WHERE recommendation_id = '$id'  //The single quotes are essential for safety!
    ) b ON (tbl_brokerage_names.brokerage_id = b.brokerage_id) 
  GROUP BY tbl_brokerage_names.brokerage_id WITH ROLLUP
  ORDER BY tbl_brokerage_names.short_name ASC
";

The GROUP BY .. WITH ROLLUP will add an extra line to the result with all NULL's for the non aggregated columns and a grand total count.

If you have any lines where rowcount > 0 then you need to add extra clauses from table b to the group by clause to prevent MySQL from hiding arbitrary rows.
Table tbl_brokerage_names is already fully defined because you are grouping by the primary key.