sql查询多表查询问题

Question

I've a following 3 table in mysql db.

project table :

p_id    p_name     p_notes   is_active  p_owner     p_owner_id  p_date 
8       project    notes     1          shibbir     18          01-01-2015

Project_assign_clients:

pac_id  assign_clients  assign_client_id    is_main_user    p_id
39      Hara Adachi     8725                1               8
40      Aihara          8726                0               8
41      Akanuma Kenji   8023                0               8
42      Bayani Patrick  7801                0               8

Project_assign_users:

pas_id  assign_users    assign_user_id  p_id
5       teustace        12              8
6       alawson         10              8
7       mfischer        14              8
8      smitchell        15              8

Now I want to get how many projects users created. e.g. I have logged in user who's id is 18 so using above table it's should be return 1 project using mysqli_num_rows. here is my query but it's return 30 strange !!

$logged_user_id = $_SESSION['user_id'];
$query = mysqli_query($link, "SELECT projects . *, projects_assign_clients . *, projects_assign_users . * FROM projects LEFT JOIN projects_assign_clients on projects.p_id = projects_assign_clients.p_id LEFT JOIN projects_assign_users ON projects.p_id = projects_assign_users.p_id WHERE projects.p_owner_id = '$logged_user_id' ");
$num =  mysqli_num_rows($query);
echo $num . " found"; // retrun 30 but should be 1 according to above table data

Answer 1

Just keep your query, but also add a sub-select to get the number of projects:

SELECT projects.*,
       projects_assign_clients.*,
       projects_assign_users.*,
       (select count(*) from projects where p_owner_id = p.p_owner_id)
FROM projects p LEFT JOIN projects_assign_clients
       ON projects.p_id = projects_assign_clients.p_id
  LEFT JOIN projects_assign_users ON projects.p_id = projects_assign_users.p_id
WHERE projects.p_owner_id = '$logged_user_id'