How to search songs_words table for songs that contain a group of words?
-
Hello,
I have an SQL database of of songs with a table that consists of 8 columns of information on words of a song. each row represents a single word from the songs lyrics:
1. `songSerial` - the serial number of the song
2. `songName` - the song name
3. `word` - a single word from the song's lyrics
4. `row_number` - the number of the row that the word is found
5. `word_position_in_row` - the number of the word in the row alone
6. `house_number` - the number of the house the word belongs to
7. `house_row` - the number of the row in the house that the word is found in
8. `word_number` - the number of the word out of all the songs lyrics
example: { 4 , The Scientist , secrets , 8 , 4 , 2 , 1 , 37 }
Now I want to query all the songs that contains a group of words. For instance all the words that have the sentence: "I Love You" in them. It must be in that order and not from different rows or houses.
Can anyone help ?
Thank you
-
Please post create table statement and inserts with sample data.
- Davismarsel - Monday, November 27, 2017 3:43 PM
Hello,
I have an SQL database of of songs with a table that consists of 8 columns of information on words of a song. each row represents a single word from the songs lyrics:
1. `songSerial` - the serial number of the song
2. `songName` - the song name
3. `word` - a single word from the song's lyrics
4. `row_number` - the number of the row that the word is found
5. `word_position_in_row` - the number of the word in the row alone
6. `house_number` - the number of the house the word belongs to
7. `house_row` - the number of the row in the house that the word is found in
8. `word_number` - the number of the word out of all the songs lyrics
example: { 4 , The Scientist , secrets , 8 , 4 , 2 , 1 , 37 }
Now I want to query all the songs that contains a group of words. For instance all the words that have the sentence: "I Love You" in them. It must be in that order and not from different rows or houses.
Can anyone help ?
Thank you
I did this once for an automotive parts system and it turned out to be incredibly fast (sub-second on a huge table). The table was a bit more normalized but, if you don't mind an additional index, I believe we can still keep the speed up. I need some more information though...
1. Is there a maximum number of words to search for?
2. Can you post the CREATE TABLE statement for this table including ALL indexes?
3. Are you allowed to used dynamic SQL provided that I also show you how to avoid things like SQL Injection in the process?
4. Can you attach a Tab Delimited and compressed zip file of a couple of thousand rows from that table so that I can have something to test with? Of course, it would also be convenient if the fields of data in the file matched the left to right order of the fields listed top to bottom in the CREATE TABLE statement. It would also be good if you made sure there were enough data for at least 2 complete songs but more, if possible, to ensure that I've remembered the code correctly.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
Thanks.There isn't a maximum number of words.. it's dynamic.
I'm allowed to use everything
I have attached 2 scripts in text files: 1 for creating the database and table , and the second for inserting some data of 3 songs.Hope you manage it Thank you
David
-
This seems like homework and MySQL.
Is any of those assumptions correct? -
It's actually an experiment of an app I am creating in java for managing songs and lyrics. Nothing to do with homework or exercises.
And yes I am working with MySQL workbench 6.0 - Davismarsel - Tuesday, November 28, 2017 2:06 PM
You do realize that there are some pretty serious differences between SQL Server (and this is a Microsoft SQL Server site) and MySQL, correct? I don't even know if MySQL can use CTEs (Common Table Expressions), never mind the cascading CTEs that I was going to demonstrate.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, November 28, 2017 3:17 PM
Apparently, Release 8.0 of MySQL introduced CTEs to the product a little over a year ago.
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
https://dev.mysql.com/doc/refman/8.0/en/with.htmlBe a bit careful with the examples on that last link... they have some serious RBAR going on in some of those. 😉
Since CTEs are available in MySQL, I'll try to write the SQL Server example I'm thinking of for this using the data that was provided in the next day or two.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply