CODEWITHSUNDEEP
sql
Forexlead
Forexlead

Reputation: 11

Merge and order rows

I have a table in the following structure. I am writing a query to get all item_ids where key_name='topic' and key_string_value='investing', which is the simple part. select item_id from table where key_name='topic' and key_string_value='investing'

But then for all the item_ids returned above, I want to order them by the values set for each item_id in key_name='importance' and key_name='product'.The table structure is making it very difficult as I am not an SQL expert. Any help would be appreciated.

item_id      key_name       key_string_value    Key_float_value  

 1            topic           investing            null
 1            importance        null                500
 1            product           A                  null
 1            product           B                  null 
 2             topic          Starting             null
 2            product            B                 null
 2            importance       null                 300
 2            topic            retail              null
 3            importance      null                  400 
 3            topic           investing             null
 3            product           C                   null 
 4            topic            Starting             null
 4            topic            investing           null 
 4            importance       null                 400
 4            product            D                   null

Upvotes: 0

Views: 35

Answers (2)

Stan
Stan

Reputation: 983

@Schwern is on right - your structure should be normalized, and the names should be better too. All this makes me think: homework.

The answer to the homework question is a self join, and looks like this:

select t1.item_id , imp.key_float_value, prd.key_string_value 
from [table] t1
    LEFT OUTER JOIN [table] imp on imp.item_id = t1.item_id and imp.key_name='importance' 
    LEFT OUTER JOIN [table] prd on prd.item_id = t1.item_id and prd.key_name='product' 
where t1.key_name='topic' and t1.key_string_value='investing'
ORDER BY imp.key_float_value, prd.key_string_value

The square brackets on `[table] are because the use of the table keyword as the table name requires the name to be delimited. Square brackets for TSQL. Others use double quotes (")

Upvotes: 1

Schwern
Schwern

Reputation: 165396

You have a very poorly design table that will be slow and difficult to work with. SQL is not a key/value store; it works on rows, columns and relationships. Rather than fight it, I would suggest redesigning it. Either use a NoSQL database which is easier to use and works more like normal programming data structures, or redesign it.

Here's the redesign I would suggest.

CREATE TABLE item (
    id                  INTEGER PRIMARY KEY,
    importance          INTEGER DEFAULT 0
);

CREATE TABLE item_topics (
    item_id             INTEGER REFERENCES item(id),
    topic               TEXT NOT NULL
);

CREATE TABLE item_products (
    item_id             INTEGER REFERENCES item(id),
    product             TEXT NOT NULL
);

The item itself, and any scalar (ie. single value) attributes go into one table. Anything which can be a list (products and topics) needs its own table relating each item to its elements. If this seems clunky, that's because it is, but that's how SQL works.

To find all items whose topic is investing, you have to join on the item_topics table.

SELECT item.id
FROM item
JOIN item_topics ON item.id = item_topics.id
WHERE topic = 'investing'

Then to order them, add ORDER BY item.importance.

Upvotes: 0

Related Questions