Manish Manish - 2 years ago 81
SQL Question

Joining tables to get count in api request

I want to create an sql query for following scenario.

I've 3 tables

request
,
contract_template
and
contract_type
.
Request
table has
contract_template_id
as foreign key,
contract_template
has
contract_type_id
as foreign key. I want to get count of each contract type based on the records in request table.

This is what I have tried so far:

$data['contractStatus'] = DB::table('request') -> join('configuration', 'request.contract_status_id', '=', 'configuration.id') ->join('contract_template', 'request.contract_template_id', '=', 'contract_template.id') ->join('contract_type', 'contract_template.contract_type_id', '=', 'contract_type.id') ->select('contract_type.name', 'configuration.value')->groupBy('contract_type.name', 'configuration.value')->get();

Answer Source

I think this is what you are expecting, as far as I understand your question you wanted a sql query. Check the below query.

select contract_type.name,count(contract_type.name) from request 
inner join contract_template  on request.contract_template_id =contract_template.contract_template_id 
inner join contract_type  on contract_template.contract_type_id =contract_type.contract_type_id
group by contract_type.name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download