database inner join query

Question

I want to fetch crimes from crm_ms_fir table where condition should be based on category of crime database detail →

crm_ms_fir(fir_id,crime_id)
crm_ms_crime(crime_id,crime_category_id,crime_name)
crm_ms_category(category_id,category_name)

Example: give me all fir reports where crime came under crime_category_id = 2

SELECT fir.fir_id,
       fir.crime_id,
       crm.crime_name
  FROM crm_ms_fir fir
 INNER JOIN crm_ms_crime crm
 INNER JOIN crm_ms_crime_category
    ON crm.category_id=3

This the sample query which I have written .

Are you using MySQL or Oracle? Please tag your question appropriately. — Gordon Linoff
– Gordon Linoff, Commented May 7, 2015 at 11:54

Bfcm · Accepted Answer · 2015-05-07 11:57:44Z

1

Actually you don't need to join crm_ms_crime_category because you have category_id in crm_ms_crime and don't use anything from crm_ms_crime_category. Also ON condition is missed. So try simply this:

SELECT fir.fir_id, fir.crime_id, crm.crime_name
FROM crm_ms_fir fir
INNER JOIN crm_ms_crime crm ON fir.crime_id = crm.crime_id
WHERE crm.category_id=3

answered May 7, 2015 at 11:57

Bfcm

2,7462 gold badges29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2015-05-07 11:59:46Z

0

You missed the JOIN conditions, and crm_ms_category is not needed here.

SELECT fir.fir_id, fir.crime_id, crm.crime_name
FROM crm_ms_fir fir
  INNER JOIN crm_ms_crime crm ON fir.crime_id = crm.crime_id
WHERE crm.category_id=2

answered May 7, 2015 at 11:59

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

database inner join query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related