SQLite - Filtering query with complex jointure table

Question

Good afternoon everyone,

I work on a project which uses a SQLite3 database and it is generated with Doctrine (ORM in PHP).

Underground station table contains all stations in Paris:

CREATE TABLE underground_station (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    long_name VARCHAR(255) NOT NULL 
); 

Line table contains all lines in Paris:

CREATE TABLE line ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    commercial_name VARCHAR(255) NOT NULL ); 

This table associates the metro lines according to the serving station:

CREATE TABLE line_association ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    underground_station_id INT NOT NULL, 
    line_id INT NOT NULL, 
    is_terminus BOOL NOT NULL, 
    CONSTRAINT fk_association_underground_station FOREIGN KEY (underground_station_id) REFERENCES underground_station(id), 
    CONSTRAINT fk_association_line FOREIGN KEY (line_id) REFERENCES line(id) ); 

I have a query to returns underground station name, the lines served there and whether it is a terminus:

SELECT u.long_name, group_concat(l.commercial_name) as "lines", la.is_terminus
FROM underground_station u
    JOIN line_association la on u.id = la.underground_station_id
    JOIN line l on la.line_id = l.id
GROUP BY u.id;

Query result:

+-------------------------+------------------+-------------+
|long_name                | lines            | is_terminus |
+-------------------------+------------------+-------------+
|CHARLES DE GAULLE ETOILE | M6,M2,M1         | 0           |
+-------------------------+------------------+-------------+
|CHATEAU DE VINCENNES     | M1               | 1           |
+-------------------------+------------------+-------------+
|CONCORDE                 | M12,M1,M8        | 0           |
+-------------------------+------------------+-------------+
|FRANKLIN-D.ROOSEVELT     | M9,M             | 0           |
+-------------------------+------------------+-------------+
|LA DEFENSE-GRANDE ARCHE  | M1               | 1           |
+-------------------------+------------------+-------------+
|NATION                   | M2,M9,M6,M1      | 0           |
+-------------------------+------------------+-------------+
|CHATELET                 | M14,M1,M7,M11,M4 | 0           |
+-------------------------+------------------+-------------+

This query is working perfecly. My question is how to returns same data which contains only underground stations with his services when I select a specific underground line like 'M1' ?

I have found this possibility but I have wrong data beacause "connections" returns always returns "1" even though undergroupd stations have 2 or more connections:

SELECT underground_station.long_name,
   (SELECT count(line_id)
    FROM line_association
    GROUP BY underground_station_id
    HAVING count(line_id)) AS "connections",
    is_terminus
FROM underground_station
JOIN line_association la on underground_station.id = la.underground_station_id
JOIN line l on la.line_id = l.id
WHERE l.commercial_name = 'M1';

Query result:

+-------------------------+-------------+-------------+
|long_name                | connections | is_terminus |
+-------------------------+-------------+-------------+
|CHARLES DE GAULLE ETOILE | 1           | 0           |
+-------------------------+-------------+-------------+
|CHATEAU DE VINCENNES     | 1           | 1           |
+-------------------------+-------------+-------------+
|CONCORDE                 | 1           | 0           |
+-------------------------+-------------+-------------+
|FRANKLIN-D.ROOSEVELT     | 1           | 0           |
+-------------------------+-------------+-------------+
|LA DEFENSE-GRANDE ARCHE  | 1           | 1           |
+-------------------------+-------------+-------------+
|NATION                   | 1           | 0           |
+-------------------------+-------------+-------------+
|CHATELET                 | 1           | 0           |
+-------------------------+-------------+-------------+

I have try with "LIKE" condition, but the results contains M14, M13, M12, M11 lines when I try to found only M1 underground stations and his connections.

I have try also "instr(lines, 'M1')" but it only returns the data linked to the "M1" underground line.

Do you have any idea how to get the correct values ​​when I filter by underground line?

Answer 1

I think you want a HAVING clause:

SELECT u.long_name, group_concat(l.commercial_name) as "lines", la.is_terminus
FROM underground_station u
    JOIN line_association la on u.id = la.underground_station_id
    JOIN line l on la.line_id = l.id
GROUP BY u.id
HAVING MAX(l.commercial_name = 'M1') = 1