CODEWITHSUNDEEP
sql
Andrei Vasilev
Andrei Vasilev

Reputation: 607

How to find number of references given another table in sql?

I have two tables:

person table                            accident table  

pid name    phone                    acc_id pid type
1   Mike    3123223232                  132 1   car
2   Kyle                                133 3   snow
3   Nick    3124567654                  134 4   cold
4   John    3124566663                  135 2   sun
5   Pety    4234345453                  136 3   hot
                                        137 2   sun
                                        138 3   snow
                                        139 2   cold
                                        140 1   hot

I need to find all accidents acc_id with a reference to each other that incurred to the same person given that she has a valid telephone number

So the result would be the following:

acc_id  reference
133     136
133     138
136     133
136     138
138     133
138     136
132     140
140     132

So, person with pid = 3 had accidents 133, 136, 138 and this person has a phone, thus these three acc_id refer to each other. Next, pid = 2 also had three accidents, however since her phone number is unknown, we do not include her. Next, pid = 1 had two accidents 132, 140 and she has a phone number , so we include her accident numbers.

I know a method how to write a query to do this (for the sake of space I did not include), but it includes joining these tables two times and I think that there must be a more efficient way. Can anybody help me?

Upvotes: 1

Views: 59

Answers (1)

Kevin DiTraglia
Kevin DiTraglia

Reputation: 26078

How about something like this? (not sure if this is what you already had)

select acc1.acc_id, acc2.acc_id as reference 
from accidents acc1
inner join accidents acct2 on acc1.pid = acc2.pid and acc1.acc_id <> acc2.acc_id
inner join people on people.pid = acc1.pid
where people.phone <> ""

Upvotes: 1

Related Questions