How to use all records from another table as counting columns?

Question

I have 4 tables:

location:

location_id   name
------------------------
1             France

device:

device_id     location_id    model_id
-------------------------------------
1             1              1
2             1              2
3             1              3

model:

model_id      family_id      name
-------------------------------------
1             1              C-max
2             1              S-max
3             2              Vectra

and family:

family_id    name
---------------------
1            Ford
2            Opel

I need to build a complicated SQL query now. As the result, I would like to receive this:

location_id    name      Ford      Opel
------------------------------------------
1              France    2         1

Is it possible to do it in SQL at all? I see there there problems:

• About using other table records as columns in the query

• About nested tables

• About counting the elements (count function?)

Any comments/reference materials will be for me helpful. I do not await the final code.

Answer 1

In SQL queries the columns are fix. You get more or less rows depending on data, not columns. But that doesn't matter, because SQL is about to get data not to display it. The latter is a task for the GUI layer.

So get the desired data, which is the number of models per location and family mainly.

select l.location_id, l.name as location_name, f.name as family_name, count(*) as models
from location l
join device d on d.location_id = l.location_id
join model m on m.model_id = d.model_id
join family f on f.family_id = m.family_id
group by l.location_id, l.name, f.name
order by l.location_id, l.name, f.name;

This is all you need from the database. How to show the data is a task for your programm, a Delphi app in your case. So use Delphi to read the data with above query and fill your grid in a simple loop.