CODEWITHSUNDEEP
mysqlsql
AGE
AGE

Reputation: 3792

SQL find the most recurring element based on different table relations

I have a question regarding how to do an SQL query. I wrote a sample database that I am using here, I am trying to keep things simple for all of you who wish to help.

Officer              Permit               Vehicle              Dispatch

Oid | Dname | Rank   Oid | Type | Model   Vid | Type | Model   Did | Oid | Location
------------------   ------------------   ------------------   --------------
1   | John  |  Jr    1     D1     Ford    1     D1     Ford    1     1      Hill
2   | Jack  |  Sr    1     D2     Ford    2     D2     Ford    2     2      Beach
3   | Jay   |  Jr    2     D1     Ford    3     D3     Ford    3     3      Post
4   | Jim   |  Jr    3     D1     Ford    4     D4     Ford    4     1      Beach
5   | Jules |  Sr    5     D1     Ford    5     D5     Ford    5     2      Hill
                     1     D3     Ford                         6     4      Post
                     2     D2     Ford                         7     5      Hill
                     4     D1     Ford                         8     5      Beach
                     1     D5     Ford                         9     2      Post

The relation between the tables are:

Officer - lists the officer by OID(officer ID)/Name/Rank where Sr is highest, Jr is lowest.
Permit - Officers are required to have a permit depending on the vehicle they will be using, Oid for Officer ID, Type for the vehicle and Model.
Vehicle - Vid for vehicle ID, Type and Model
Dispatch - Did for Dispatch ID, keeps track of which officer (Oid) was dispatched to which location (Location)

Question: I need to know a couple of things from here. First is how do I know which officer is permitted to drive all vehicle types? Second is How do I know which officer has been dispatched to all the dispatched locations?

Writing these two queries has been a nightmare for me, I have tried to join different tables but I still cannot get the most recurring element from either (I don't know how!) any assistance will be much appreciated!

Upvotes: 1

Views: 314

Answers (1)

Barmar
Barmar

Reputation: 782653

First question:

select Oid, count(*) type_count
from Permit
group by Oid
having type_count = (select count(distinct Type, Model) from Vehicle)

Second:

select Oid, count(*) location_count
from Dispatch
group by Oid
having location_count = (select count(distinct Location) from Dispatch)

See a pattern?

Upvotes: 2

Related Questions