Skip to content

Instantly share code, notes, and snippets.

@qcom

qcom/setup.sql

Last active August 29, 2015 14:05
Download ZIP

Revisions

  1. qcom revised this gist Aug 12, 2014. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -24,7 +24,8 @@ CREATE TABLE IF NOT EXISTS items_categories (

    -- now-functioning query

    SELECT item_id, title, industries, array_agg(name) FROM
    SELECT item_id, title, industries, array_agg(name)
    FROM
    items_categories ic
    INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries FROM
    items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
  2. qcom revised this gist Aug 12, 2014. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -26,7 +26,8 @@ CREATE TABLE IF NOT EXISTS items_categories (

    SELECT item_id, title, industries, array_agg(name) FROM
    items_categories ic
    INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries FROM
    items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) jt
    ON (ic.item = jt.item_id)
    INNER JOIN categories ON category = categories.id GROUP BY item_id, title, industries;
  3. qcom revised this gist Aug 12, 2014. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -26,8 +26,7 @@ CREATE TABLE IF NOT EXISTS items_categories (

    SELECT item_id, title, industries, array_agg(name) FROM
    items_categories ic
    INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) jt
    ON (ic.item = jt.item_id)
    INNER JOIN categories ON category = categories.id GROUP BY item_id, title, industries;
  4. qcom revised this gist Aug 12, 2014. 1 changed file with 7 additions and 3 deletions.
    10 changes: 7 additions & 3 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -24,6 +24,10 @@ CREATE TABLE IF NOT EXISTS items_categories (

    -- now-functioning query

    SELECT item_id, title, industries, array_agg(name) FROM items_categories ic INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) jt ON (ic.item = jt.item_id) INNER JOIN categories ON category = categories.id GROUP BY item_id, title, industries;
    SELECT item_id, title, industries, array_agg(name) FROM
    items_categories ic
    INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) jt
    ON (ic.item = jt.item_id)
    INNER JOIN categories ON category = categories.id GROUP BY item_id, title, industries;
  5. qcom revised this gist Aug 12, 2014. 1 changed file with 1 addition and 11 deletions.
    12 changes: 1 addition & 11 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -22,17 +22,7 @@ CREATE TABLE IF NOT EXISTS items_categories (
    industry integer REFERENCES industries(id)
    );

    -- attempted query

    SELECT items.id, title, array_agg(industries.name) AS industries, array_agg(categories.name) as categories
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31 GROUP BY items.id;

    SELECT items.id, title, industries.name, categories.name
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31;
    -- now-functioning query

    SELECT item_id, title, industries, array_agg(name) FROM items_categories ic INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
  6. qcom revised this gist Aug 12, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -34,6 +34,6 @@ FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31;

    SELECT * FROM items_categories INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    SELECT item_id, title, industries, array_agg(name) FROM items_categories ic INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) ON items_categories.item = item_id;
    WHERE items.id = 31 GROUP BY items.id) jt ON (ic.item = jt.item_id) INNER JOIN categories ON category = categories.id GROUP BY item_id, title, industries;
  7. qcom revised this gist Aug 12, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -36,4 +36,4 @@ WHERE items.id = 31;

    SELECT * FROM items_categories INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) ON items_categories.item = foo.item_id;
    WHERE items.id = 31 GROUP BY items.id) ON items_categories.item = item_id;
  8. qcom revised this gist Aug 12, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -34,6 +34,6 @@ FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31;

    SELECT * FROM items_categories INNER JOIN ((SELECT items.id as item_id, title, array_agg(industries.name) as industries
    SELECT * FROM items_categories INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) AS foo) ON items_categories.item = foo.item_id;
    WHERE items.id = 31 GROUP BY items.id) ON items_categories.item = foo.item_id;
  9. qcom revised this gist Aug 12, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -34,6 +34,6 @@ FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31;

    SELECT items.id, title, array_agg(industries.name) as industries
    SELECT * FROM items_categories INNER JOIN ((SELECT items.id as item_id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id;
    WHERE items.id = 31 GROUP BY items.id) AS foo) ON items_categories.item = foo.item_id;
  10. qcom revised this gist Aug 12, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -34,6 +34,6 @@ FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31;

    SELECT items.id, title, industries.name, array_agg(industries.name) as industries
    SELECT items.id, title, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id;
  11. qcom revised this gist Aug 12, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -34,6 +34,6 @@ FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31;

    SELECT items.id, title, industries.name
    SELECT items.id, title, industries.name, array_agg(industries.name) as industries
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31;
    WHERE items.id = 31 GROUP BY items.id;
  12. qcom revised this gist Aug 12, 2014. 1 changed file with 10 additions and 1 deletion.
    11 changes: 10 additions & 1 deletion setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -27,4 +27,13 @@ CREATE TABLE IF NOT EXISTS items_categories (
    SELECT items.id, title, array_agg(industries.name) AS industries, array_agg(categories.name) as categories
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31 GROUP BY items.id;
    WHERE items.id = 31 GROUP BY items.id;

    SELECT items.id, title, industries.name, categories.name
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31;

    SELECT items.id, title, industries.name
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31;
  13. qcom revised this gist Aug 12, 2014. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,8 @@ CREATE TABLE IF NOT EXISTS items_categories (
    industry integer REFERENCES industries(id)
    );

    -- attempted query

    SELECT items.id, title, array_agg(industries.name) AS industries, array_agg(categories.name) as categories
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
  14. qcom revised this gist Aug 12, 2014. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -23,4 +23,6 @@ CREATE TABLE IF NOT EXISTS items_categories (
    );

    SELECT items.id, title, array_agg(industries.name) AS industries, array_agg(categories.name) as categories
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category WHERE items.id = 31 GROUP BY items.id;
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
    WHERE items.id = 31 GROUP BY items.id;
  15. qcom revised this gist Aug 12, 2014. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -20,4 +20,7 @@ CREATE TABLE IF NOT EXISTS categories (
    CREATE TABLE IF NOT EXISTS items_categories (
    item integer REFERENCES items(id),
    industry integer REFERENCES industries(id)
    );
    );

    SELECT items.id, title, array_agg(industries.name) AS industries, array_agg(categories.name) as categories
    FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category WHERE items.id = 31 GROUP BY items.id;
  16. qcom created this gist Aug 12, 2014.
    23 changes: 23 additions & 0 deletions setup.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    CREATE TABLE IF NOT EXISTS items (
    id serial PRIMARY KEY
    );

    CREATE TABLE IF NOT EXISTS industries (
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
    );

    CREATE TABLE IF NOT EXISTS items_industries (
    item integer REFERENCES items(id),
    industry integer REFERENCES industries(id)
    );

    CREATE TABLE IF NOT EXISTS categories (
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
    );

    CREATE TABLE IF NOT EXISTS items_categories (
    item integer REFERENCES items(id),
    industry integer REFERENCES industries(id)
    );