StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>SELECT COUNT(*) AS Stuff_Qnt, C.Category_ID, MC.Category_Name FROM MC JOIN (SELECT C1.Category_ID FROM A JOIN C1 ON A.Main_Key = C1.Main_Key UNION ALL SELECT C2.Category_ID FROM A JOIN C2 ON A.Main_Key = C2.Main_Key ) AS C ON C.Category_ID = MC.Category_ID GROUP BY C.Category_ID, MC.Category_Name ORDER BY C.Category_ID, Stuff_Qnt; </code></pre> <p>You need the categories from joining A with C1 and the categories from joining A with C2, and you definitely don't want a Cartesian product such as you'd get with LEFT OUTER JOIN, so you take the UNION of the lists of category IDs, and then aggregate and join with MC.</p> <pre><code>CREATE TABLE A ( main_key INTEGER NOT NULL PRIMARY KEY, stuff_id INTEGER NOT NULL ); INSERT INTO A VALUES(1, 7); INSERT INTO A VALUES(2, 6); INSERT INTO A VALUES(3, 3); CREATE TABLE MC ( category_id INTEGER NOT NULL PRIMARY KEY, category_name VARCHAR(10) NOT NULL ); INSERT INTO mc VALUES(1, "blablbl"); INSERT INTO mc VALUES(2, "asas"); INSERT INTO mc VALUES(3, "asasa"); CREATE TABLE C1 ( category_id INTEGER NOT NULL REFERENCES mc, main_key INTEGER NOT NULL REFERENCES a ); INSERT INTO c1 VALUES(1, 1); INSERT INTO c1 VALUES(1, 2); INSERT INTO c1 VALUES(3, 1); CREATE TABLE C2 ( category_id INTEGER NOT NULL REFERENCES mc, main_key INTEGER NOT NULL REFERENCES a ); INSERT INTO c2 VALUES(2, 3); INSERT INTO c2 VALUES(2, 1); SELECT COUNT(*) AS Stuff_Qnt, C.Category_ID, MC.Category_Name FROM MC JOIN (SELECT C1.Category_ID FROM A JOIN C1 ON A.Main_Key = C1.Main_Key UNION ALL SELECT C2.Category_ID FROM A JOIN C2 ON A.Main_Key = C2.Main_Key ) AS C ON C.Category_ID = MC.Category_ID GROUP BY C.Category_ID, MC.Category_Name ORDER BY C.Category_ID, Stuff_Qnt; </code></pre> <p>Output:</p> <pre><code>2 1 blablbl 2 2 asas 1 3 asasa </code></pre> <hr> <p>Here is a further test with two extra rows in A, and corresponding rows in C1 and C2. There are two queries tested, mine and the <a href="https://stackoverflow.com/a/13840710/15168">query</a> by <a href="https://stackoverflow.com/users/1053633/dkkumargoyal">dkkumargoyal</a>.</p> <pre><code>CREATE TABLE A(main_key INTEGER NOT NULL PRIMARY KEY, stuff_id INTEGER NOT NULL); INSERT INTO A VALUES(1, 7); INSERT INTO A VALUES(2, 6); INSERT INTO A VALUES(3, 3); INSERT INTO A VALUES(4, 3); INSERT INTO A VALUES(5, 3); CREATE TABLE MC(category_id INTEGER NOT NULL PRIMARY KEY, category_name VARCHAR(10) NOT NULL); INSERT INTO mc VALUES(1, "blablbl"); INSERT INTO mc VALUES(2, "asas"); INSERT INTO mc VALUES(3, "asasa"); CREATE TABLE C1(category_id INTEGER NOT NULL REFERENCES mc, main_key INTEGER NOT NULL REFERENCES a); INSERT INTO c1 VALUES(1, 1); INSERT INTO c1 VALUES(1, 2); INSERT INTO c1 VALUES(3, 1); INSERT INTO c1 VALUES(3, 4); INSERT INTO c1 VALUES(1, 4); INSERT INTO c1 VALUES(1, 5); CREATE TABLE C2(category_id INTEGER NOT NULL REFERENCES mc, main_key INTEGER NOT NULL REFERENCES a); INSERT INTO c2 VALUES(2, 3); INSERT INTO c2 VALUES(2, 1); INSERT INTO c2 VALUES(2, 5); SELECT COUNT(*) AS Stuff_Qnt, C.Category_ID, MC.Category_Name FROM MC JOIN (SELECT C1.Category_ID FROM A JOIN C1 ON A.Main_Key = C1.Main_Key UNION ALL SELECT C2.Category_ID FROM A JOIN C2 ON A.Main_Key = C2.Main_Key ) AS C ON C.Category_ID = MC.Category_ID GROUP BY C.Category_ID, MC.Category_Name ORDER BY C.Category_ID, Stuff_Qnt; -- Query by dkkumargoyal SELECT COUNT(DISTINCT A.stuff_id) AS stuff_qnt, MC.category_id, MC.category_name FROM A LEFT JOIN C1 on a.main_key = c1.main_key -- USING(main_key) LEFT JOIN C2 on a.main_key = c2.main_key -- USING(main_key) LEFT JOIN MC ON (C1.category_id = MC.category_id OR C2.category_id = MC.category_id) GROUP BY MC.category_id, mc.category_name ORDER BY MC.Category_id, stuff_qnt; -- stuff_qnt added for standard compatibility </code></pre> <p>The changes made were necessary for the code to work on the test DBMS (Informix 11.70.FC6).</p> <p>Results 1:</p> <pre><code>4 1 blablbl 3 2 asas 2 3 asasa </code></pre> <p>Results 2: </p> <pre><code>3 1 blablbl 2 2 asas 2 3 asasa </code></pre> <p>I think my result is correct and the other not, mainly because the alternative depends on <code>A.Stuff_ID</code> being unique when the question stipulates that it is not unique (and the additional rows of data make it non-unique).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload