similarity - SQLite combine values of similar records into one -

-

in sqlite database have table called tracks made of following columns: artist, track, genre1, genre2, genre3. table contains many values have same artist , track values different genre1, genre2, genre3 values. example below:

artist | track   | genre1  | genre2 | genre3 abba   | song 1  | rock    | rock   |  rock u2     | song 4  | rock    | rock   |  rock abba   | song 1  | pop     | pop    |  pop u2     | song 4  | pop     | pop    |  pop abba   | song 1  | 70s     | 70s    |  70s u2     | song 4  | 90s     | 90s    |  90s

i need create sqlite statement amalgamate unique genre values artist , track same, example shown below:

artist | track   | genre1  | genre2 | genre3 abba   | song 1  | rock    | pop    |  70s u2     | song 4  | pop     | rock   |  90s

any hugely appreciated.

unfortunately looks suffering poor database design. in design above limited 3 genre per song, , when 1 genre applicable song repeat same value across genre fields. in design should able have many or little genre entries per song, can reduce amount of data required store genre values.

so lets talk many-to-many relations , mapping tables. many-to-many relationship in situation many unique genre values belonging many unique song entries. should true reverse, because have many unique songs belonging many (or possibly none) genres.

so how achieve this... first lets make table called genres. should have 2 fields: integer primary key, , string genre value, unique constraint on latter. next need mapping table (for futer reference lets call songgenre), table has 2 foreign key fields, 1 referencing song table , 1 referencing genre table. each record genre belongs song, , can have multiple entries per song.

once mapping table setup can achieve want 

select artist, track, group_concat(genre.genrename)  song  join songgenre using (songid)  join genre using (genreid)  group artist, track;

Comments

Post a Comment

Popular posts from this blog

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null
Read more

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops',
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens
Read more