How to select all rows that are related to satisfied condition-CodePudding

I do not even knot how to ask what i need so I will demonstrate on data:

I have the following data in tblA:

the query need to result all rows where BMW 1 SERIES was involved in the accident with other cars, where the other car could also be BMW 1 SERIES. The End result should look like this:

Each accident has a ref number "AccRef" but there are multiple cars involved in an accident

Thanks

CodePudding user response：

This will query the first requirement of the BMW 1 SERIES rows:

select * from ACCIDENTS where CarMake like '%BMW 1 SERIES%'

Then you want to select the involved row with the same AccRef.
For best performances, use inner join on the same AccRef from the inner query mentioned above.

select ACC.* from ACCIDENTS ACC
inner join (
   select * from ACCIDENTS where CarMake like '%BMW 1 SERIES%'
) ACC_BMW 
on ACC.AccRef = ACC_BMW.AccRef

EDIT: I used the name table ACCIDENTS instead of your tblA

CodePudding user response：

Or also:

SELECT
  *
FROM accidents
WHERE accref IN (
  SELECT
    accref
  FROM accidents
  WHERE carmaker ='BMW 1 SERIES'
);