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.

i assume you are thinkling of a query that "selects" additional columns based on the data found in the tables? — Takarii, Jan 21 '16 at 10:40
yes, I need to add variable number of columns depending on record count of the family table — Jacek Krawczyk, Jan 21 '16 at 10:42
Don't try to do that... The SQL way works in the other direction, i.e. a separate row for each brand in your case. — jarlh, Jan 21 '16 at 10:43
have a look at this questions answer, it might help you http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql — Takarii, Jan 21 '16 at 10:44
@Takarii: it seems be ideal for me. Thank you very much. I am using the PostgreSQL, where [dynamic SQL](http://www.postgresql.org/docs/current/static/ecpg-dynamic.html) is also possible to use. Do you think I may use the dynamic method? Actually, the static one is also superb for me... — Jacek Krawczyk, Jan 21 '16 at 10:51
@JacekKrawczyk the principle should be the same, its the pivot method you are looking for to give you that dynamic generation. The static only works for known columns - which could work for you given a limited number - but there are a ton of car models out there :) — Takarii, Jan 21 '16 at 10:55
@Takarii: cars are only example here :) But the principle is the same - I have actually only 4 "cars" defined since years. — Jacek Krawczyk, Jan 21 '16 at 11:02
@JacekKrawczyk Ah, I understand. In that case i see no reason why you can't do this in postgre, you jsut need to port it over. In general its mostly the same, but you would need to check certain keywords. — Takarii, Jan 21 '16 at 11:04

score 1 · Answer 1 · answered Jan 21 '16 at 11:28

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.

answered Jan 21 '16 at 11:28

Thorsten Kettner

89,309
7
49
73

Then your data is incomplete. Here is an SQL fiddle, showing the query works: http://www.sqlfiddle.com/#!9/dc28e/1 – Thorsten Kettner Jan 21 '16 at 12:27
Thanks for link, I did not know this page before... I will check my code, although I prefer to add more columns in one location record instead of duplicate it for each "car". – Jacek Krawczyk Jan 21 '16 at 12:36
If you want the data prêt-à-porter from the database, then your only option is a dynamic query: Get all "families" first, then use them to build the final query with string operations in Delphi. – Thorsten Kettner Jan 21 '16 at 13:32
Not necessary. I see that the dynamic query is a bit difficult to me, but I can well define in code my "families" which I want to see. I tried also with the static solution, but to see how many Opels and Fords I have, I need... 2 records for one location (one is null, the other has value). So the results is the same like in your sample code... – Jacek Krawczyk Jan 21 '16 at 13:38
Well, of course you can hard-code the families. Then everytime you introduce a new family, you must change your program. Possible, but of course not what I would recommend. To write a loop to fetch the data is a very simple task. And building a dynamic query is also very easy. It simply means you take Delphi and write `"... as " + Family` instead of `"... as Ford"`. – Thorsten Kettner Jan 21 '16 at 13:49

score 0 · Accepted Answer · answered Jan 21 '16 at 15:12

Thank you all for your helpful tips.

I solved my problem using the static method and the code published by @Matt. Because somebody else may looking for the solution, I paste here my working query for PostgreSQL:

SELECT DISTINCT t.location_id, t.name, SUM(t.ford) AS ford, SUM(t.opel) as opel
FROM(
SELECT l.location_id, l.name, 
(SELECT COUNT(m.family_id) WHERE m.family_id = '1') AS ford, 
(SELECT COUNT(m.family_id) WHERE m.family_id = '2') AS opel 
FROM location l
INNER JOIN device d ON l.location_id = d.location_id
INNER JOIN model m ON d.model_id = m.model_id
INNER JOIN family f ON m.family_id = f.family_id
GROUP BY l.location_id, l.name, m.family_id
) t
GROUP BY t.location_id;

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

2 Answers2