Doing a SELECT * on one table and count() on two other.

Hi,

Given 3 tables, TABLE_A, TABLE_B, TABLE_C
I need all the data in TABLE_A so I do a simple

SELECT * FROM TABLE_A

one of the value it returns is an ID shared by all 3 tables.
The ID is unique to TABLE_A, but not to TABLE_B and TABLE_C

So as I loop around TABLE_A I need to know if there is any data in TABLE_B or TABLE_C

SELECT * FROM TABLE_A;
while( ...)
{
  SELECT COUNT(*) as count_b FROM TABLE_B where ID = $table_a_id;
  if( as count_b > 0 ){
    //  do something
  }
  SELECT COUNT(*) as count_c FROM TABLE_C where ID = $table_a_id;
  if( as count_c > 0 ){
    //  do something else
  }
}

Code (markup):

But I want to get rid of those 2 extra queries and include them in my original query.
Because TABLE_B and TABLE_C are almost always empty.

So, how can I do a select all on TABLE_A and get the count of TABLE_B and TABLE_C?

Any ideas?

FFMG

 

Select count(*) from table_a inner join table_b on table_a.table_a_id = table_b.table_a_id inner join table_c on table_a.table_a_id = table_C.table_a_id

depending on how you use it, the above could work

 

I think you need to use LEFT OUTER JOIN instead. You mentioned that table b and c could be empty for a given ID in tblA, correct? You will also need a group by on your columns in tableA.

Psuedo sql
select tblA.Col1, tblA.Col2, count(b.*), count(c.*)
from tblA left join tblB ON tblA.id = tblB.id
left join tblC on tblA.id = tblC.id
group by tblA.Col1, tblA.Col2