Geek asks and answers

Query multiple tables with complex relationships

The query required for what I would like is outside my SQL knowledge, so I hope to get help here. I want postings from multiple tables to be added in postgreSQL 9.2. I know the procedure, but I do not know SQL.

This query will include 4 tables:

  • wish list: linked to one or more lists and has preferred stores
  • list_wishlist: maintains a link between lists and wishlists.
  • item: linked to the list and prefers stores
  • prefered_stores: this maintains a relationship between stores and preferred stores (each preferred store is a separate line in the prefered_store. Thus, if an item or wish list contains more than one preferred store, prefered_store.id will be the same for these lines)

Tables look like this (with inappropriate columns canceled): enter image description here

And here is what the resulting table would look like: enter image description here

Let me explain the result table:

  • item_id: simple, item id
  • item_stores_comments: store / comment pairs of all the preferred lines associated with this item (save / comment separated by a comma and pairs separated by a semicolon)
  • wishlist_stores: _ , , ( )

item_info , , , , , , , .

SQLFiddle, : http://sqlfiddle.com/#!12/9fd60 , .

+3
1

, :

WITH a AS (
  SELECT item.id, string_agg(prefered_store.store::varchar, ',') wishlist_stores
  FROM item, list_wishlist, wishlist, prefered_store
  WHERE item.list=list_wishlist.list
    AND list_wishlist.wishlist=wishlist.id
    AND wishlist.prefered_stores=prefered_store.id
  GROUP BY item.id
), b AS (
  SELECT item.id, 
    string_agg(
      prefered_store.store::varchar || ',' || prefered_store.comment,
      ' ; ') item_stores_comments
    FROM item, prefered_store
    WHERE item.prefered_stores=prefered_store.id
    GROUP BY item.id
)
SELECT a.id,item_stores_comments,wishlist_stores 
FROM a,b
WHERE a.id=b.id
+3

More articles:


All Articles