i have table called chapters 5 fields books (book1, book2, etc.) , 5 fields products (product1, product2, etc.), plus other fields shouldn't necessary this, have included key (filename) unique every record, in case.
chapters
| filename | title | book1 | book2 | book3 | book4 | book5 | product1 | product2 | product3 | product4 | product5 | |------------|---------|---------|-------|--------|-------|-------|-----------|----------|-----------|----------|----------| | file1.pdf | title1 | book1 | | | | | product4 | | | | | | file2.pdf | title2 | book9 | | | | | product1 | product4 | | | | | file3.pdf | title3 | book4 | | | | | product5 | | | | | | file4.pdf | title4 | book7 | | | | | product1 | | | | | | file5.pdf | title5 | book21 | | | | | product13 | product9 | product11 | | | | file6.pdf | title6 | book5 | | | | | product23 | product3 | | | | | file7.pdf | title7 | book675 | | | | | product45 | product6 | | | | | file8.pdf | title8 | book3 | book5 | | | | product9 | | | | | | file9.pdf | title9 | book1 | book7 | book34 | | | product77 | | | | | | file10.pdf | title10 | book22 | | | | | product3 | | | | | i need 2 queries. first list of possible books, associated products, , second mirror, possible products, list of associated books. same, mirror of information. here 1 desired end result.
| books | products | |---------|--------------------------------| | book1 | product4, product77 | | book21 | product13, product9, product11 | | book22 | product3 | | book3 | product9 | | book34 | product77 | | book4 | product5 | | book5 | product9, product23, product3 | | book675 | product45, product6 | | book7 | product77, product1 | | book9 | product1, product4 |' i have tried ton of selects (don't ask how time), following gets me close, blank book listings, , duplicates (book1 come twice, etc.).
select booklist.books, concat_ws(', ', if(length(chapters.product1),chapters.product1, null), if(length(chapters.product2),chapters.product2, null), if(length(chapters.product3),chapters.product3, null), if(length(chapters.product4),chapters.product4, null), if(length(chapters.product5),chapters.product5, null)) products (select book1 books chapters union select book2 chapters union select book3 chapters union select book4 chapters union select book5 chapters) booklist join chapters on chapters.book1 = booklist.books or chapters.book2 = booklist.books or chapters.book3 = booklist.books or chapters.book4 = booklist.books or chapters.book5 = booklist.books order booklist.books; which gives me following:
| books | products | |---------|--------------------------------| | | product13, product9, product11 | | | product1 | | | product5 | | | product1, product4 | | | product3 | | | product4 | | | product77 | | | product9 | | | product45, product6 | | | product23, product3 | | book1 | product4 | | book1 | product77 | | book21 | product13, product9, product11 | | book22 | product3 | | book3 | product9 | | book34 | product77 | | book4 | product5 | | book5 | product9 | | book5 | product23, product3 | | book675 | product45, product6 | | book7 | product77 | | book7 | product1 | | book9 | product1, product4 | so in end, how rid of empty records , combine multiple books. dataset never more 200-300 records. reading far down!
here sqlfiddle
if cannot normalize schema, "pseudo normalization on fly" using views:
create view books select `filename`, `book1` `book` chapters union select `filename`, `book2` chapters union select `filename`, `book3` chapters union select `filename`, `book4` chapters union select `filename`, `book5` chapters ; create view products select `filename`, `product1` `product` chapters union select `filename`, `product2` chapters union select `filename`, `product3` chapters union select `filename`, `product4` chapters union select `filename`, `product5` chapters ; and query on these views in similar way on normalized schema, example:
select `book`, group_concat( `product` ) products books b join products p using (`filename`) b.`book` not null , p.`product` not null group `book` ; demo: http://sqlfiddle.com/#!9/8717d/2
but warned - approach (very) slow.
best can normalize schema.
Comments
Post a Comment