I need to select all rows from one table with some information grabbed from another table. Here is what I mean:
table1:
user_id
user_name
table2:
resource_id
rel_user_id
resource_data
I need to select all users with number of resources her own. E.g.:
if I had the following data in the db:
table1:
user_id = 1, user_name = 'User1';
user_id = 2, user_name = 'User2';
table2:
resource_id = 1, rel_user_id = 1, resource_data = "foo1";
resource_id = 2, rel_user_id = 1, resource_data = "foo2";
resource_id = 3, rel_user_id = 1, resource_data = "foo3";
resource_id = 4, rel_user_id = 2, resource_data = "foo4";
selected data:
'user_id'=>1, 'user_name'=>'User1', 'num_of_resources'=>3;
'user_id'=>2, 'user_name'=>'User2', 'num_of_resources'=>1;
I can do this with multiple queries and some php coding.
Also, I can do this with one query such as
"select table1.user_id, table1.user_name, count(table2.resource_id) from table1, table2 where table1.user_id=table2.rel_user_id group by table1.user_id;"
but in this case I cannot select users that haven't any resources.
But I don't want to do this using multiple queries + additional php code cause of large amount of data selected.
Any suggestions?
