3

The query required for what I'd like to is beyond my SQL knowledge so I'm hoping to get some help here. I want to get releationships from multiple tables into one in postgreSQL 9.2. I know the procedure but I don't know the SQL.

There are 4 tables that will go into this query:

  • wishlist: is associated with one or more lists and has prefered stores
  • list_wishlist: holds the relationship between lists and wishlists
  • item: is associated with a list and has prefered stores
  • prefered_stores: this holds the relationsship between stores and prefered stores (Each prefered store is a separate row in prefered_store. Thus if an item or wishlist has more than one prefered store, prefered_store.id will be the same for those rows)

The tables look like this (with irrelevant columns removed): 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: just that, id of item
  • item_stores_comments: store/comment pairs from all prefered_stores rows associated with this item (store/comment separated by comma and pairs separated by semicolon)
  • wishlist_stores: store ids of prefered_stored of wishlists that are associated with lists that this item is in (separated by commas)

I've filled the item_info table with the actual result for the example tables above so I guess it should be clear but please let me know if you don't get something.

I've created a SQLFiddle as recommended in the comments: http://sqlfiddle.com/#!12/9fd60 It contains the same schema and values as in the images.

Twifty
  • 3,267
  • 1
  • 29
  • 54
userBG
  • 6,860
  • 10
  • 31
  • 39
  • Two comments/recommendations that will help you get the best/fastest answer. You should usually include what version of postgreSQL you are using. When trying to provide actual data and desired result, SqlFiddle can be a great way to set it up to help people play with it and figure out the query for you. Google SQLFiddle. – David S Jan 25 '13 at 18:00
  • @DavidS I've got the SQLFiddle up thanks for the suggestion never heard of it before. – userBG Jan 25 '13 at 18:40
  • What is the reason/business case that requires the id's to be paired together in the same field? What are you trying to accomplish with this? – N1tr0 Jan 25 '13 at 18:46
  • @N1tr0 It helps with migrating into AppEngine datastore (a non relational database) – userBG Jan 25 '13 at 18:50
  • This conversation should help you out with a part of this (getting the data for the item_stores_comments field): http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query It shows how to concat values from two fields plus the commas and semi-colons you need. – N1tr0 Jan 25 '13 at 19:04
  • For the wishlists_stores, I'm not sure how you are getting your results from you example since the store ids that you list (16, 2, 32) don't use the same preferred_store Id. If they did, it would be easier to grab the values you are looking for. – N1tr0 Jan 25 '13 at 19:08
  • Glad you like SqlFiddle. It can be great for questions like this. Also, just a follow up to @N1tr0 comments. Do you need this in the exact format? Or could you use PostgreSQL 9.2s JSON type? It might be easier and more portable for you. – David S Jan 25 '13 at 19:12
  • @DavidS Actually I had planned to parse those back to JSON later not knowing there was a json type in postgreSQL so definitely JSON or jsonTEXT is better. – userBG Jan 25 '13 at 19:23
  • Feel like JSON would be the way to go here. I suggest you update your OP to include that for people. Otherwise, it will get buried down here in the comments and people will not see it. – David S Jan 25 '13 at 19:57
  • @DavidS I think it would make it even more confusing that it already is so I will use Eelke's answer and just parse to json when I read out. – userBG Jan 25 '13 at 20:10
  • Well, suit yourself, but I think it would be pretty easy to just convert them to Json. See: http://www.postgresql.org/docs/devel/static/functions-json.html – David S Jan 25 '13 at 21:07

1 Answers1

3

This should do what you want:

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
Eelke
  • 20,897
  • 4
  • 50
  • 76
  • I do not have much experience with JSON and none of it in Postgres. I don't think the use of JSON will slowdown the collection of all the data for the query however the formatted result will be much larger so transferring the result could take more time. – Eelke Jan 26 '13 at 08:34