Jo_bast Jo_bast - 6 months ago 8
SQL Question

Counting one field of table in other table

I wrote a script in oracle. But it does not give me the result that i want.
I need this one, imagine i have two table. Order_table and book table.
My order table is like this

ORDER_TABLE Table

ID TYPE_ID VALUE_ID
1 11 null
2 11 null
3 11 null
4 12 null
5 11 null


Book Table

ID ORDER_TYPE DELETED
1 1 F
2 null F
3 5 F
4 5 F
5 4 F
6 4 F
7 3 T


My script is like this

Select *
From (
Select Newtable.Counter As Value_id,
o.Id As Id,
o.Type_id As Type_id
From (
Select (Count B.Order_Type) As Counter,
B.Order_Type As Id
From Book B
Where B.Deleted = 'F'
Group By B.Order_Type
Order By Count(B.Order_Type) Desc
) newtable,
order_table o
where o.id = newtable.id
and o.type_id = 11
)
order by id asc;


Result is like this.

Value_ID TYPE_ID ID
2 11 5
2 11 4
1 11 1


It is not showing that second and third id has 0 count, Have can i show 0 count too ?

Result should be like this.

Value_ID TYPE_ID ID
2 11 5
2 11 4
1 11 1
0 11 2
0 11 3

Answer

First, do not use implicit JOIN syntax(comma separated), that's one of the reason this mistakes are hard to catch! Use the proper JOIN syntax.

Second, your problem is that you need a left join, not an inner join , so try this:

Select *
  From  (Select coalesce(Newtable.Counter,0) As Value_id,
                o.Id             As Id,
                o.Type_id        As Type_id
         From order_table o
         LEFT JOIN (Select Count(B.Order_Type) As Counter, B.Order_Type As Id
                    From Book B
                    Where B.Deleted = 'F'
                    Group By B.Order_Type
                    Order By Count(B.Order_Type) Desc) newtable
           ON(o.id = newtable.id)
         WHERE o.type_id = 11)
 order by id asc;