...two views. According to the following SQL statement(see below) I'm
going to create a table
'A' storing student's name, table 'B' storing courses and table D storing cards.
Furthermore, it creates two pivot tables A2B and A2D to refer courses and cards.
I'd like to query the DB to get that result:
1|John|Doe|Italian, Spanish|12345
2|Paul|Smith|English, Italian|12345, 13579
using the views aTest and dTest it returns:
--aTest
1|John|Doe|Italian, Spanish
2|Paul|Smith|English, Italian
and
--dTest
1|John|Doe|12345
2|Paul|Smith|12345, 13579
but the vTest view doesn't return the right result:
1|John|Doe|Italian, Spanish|12345, 12345
2|Paul|Smith|English, English, Italian, Italian|12345, 13579, 12345, 13579
so could you suggest an SQL solution?
Thanks in advance, Danilo
-- START SQL FILE
CREATE TABLE A (
a_id INTEGER PRIMARY KEY NOT NULL,
a_name TEXT,
a_last TEXT);
CREATE TABLE A2B (
a2b_id INTEGER PRIMARY KEY NOT NULL,
a2b_ref_a INTEGER,
a2b_ref_b INTEGER);
CREATE TABLE B (
b_id INTEGER PRIMARY KEY NOT NULL,
b_course TEXT);
CREATE TABLE A2D (
a2d_id INTEGER PRIMARY KEY NOT NULL,
a2d_ref_a INTEGER,
a2d_ref_d INTEGER);
CREATE TABLE D (
d_id INTEGER PRIMARY KEY NOT NULL,
d_card TEXT);
INSERT INTO A VALUES(1,'John','Doe');
INSERT INTO A VALUES(2,'Paul','Smith');
INSERT INTO B VALUES(1,'English');
INSERT INTO B VALUES(2,'Italian');
INSERT INTO B VALUES(3,'Spanish');
INSERT INTO A2B VALUES(1,1,2);
INSERT INTO A2B VALUES(2,1,3);
INSERT INTO A2B VALUES(3,2,1);
INSERT INTO A2B VALUES(4,2,2);
INSERT INTO D VALUES(1,'12345');
INSERT INTO D VALUES(2,'67890');
INSERT INTO D VALUES(3,'13579');
INSERT INTO A2D VALUES(1,1,1);
INSERT INTO A2D VALUES(2,2,1);
INSERT INTO A2D VALUES(3,2,3);
CREATE VIEW aTest AS
SELECT
A.*,
group_concat(b_course, ', ')
FROM A
LEFT OUTER JOIN A2B ON a_id = a2b_ref_a
INNER JOIN B ON a2b_ref_b = b_id
GROUP BY a_id;
CREATE VIEW dTest AS
SELECT
A.*,
group_concat(d_card, ', ')
FROM A
LEFT OUTER JOIN A2D ON a_id = a2d_ref_a
INNER JOIN D ON a2d_ref_d = d_id
GROUP BY a_id;
CREATE VIEW vTest AS
SELECT
A.*,
group_concat(b_course, ', '),
group_concat(d_card, ', ')
FROM A
LEFT OUTER JOIN A2B ON a_id = a2b_ref_a
INNER JOIN B ON a2b_ref_b = b_id
LEFT OUTER JOIN A2D ON a_id = a2d_ref_a
INNER JOIN D ON a2d_ref_d = d_id
GROUP BY a_id;
-- END
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
