Forums Register Login

Joining 2 tables

+Pie Number of slices to send: Send
**I have posted this question in here but still no good answer so I am posting it in this forum.

I am kind of new to SQL. I have 2 MySQL Tables. Below is their structure.

**`Key_Hash` Table**


--


**`Key_Word` Table**




Now, below is my query


When you run the above query, you will get an output like below



The important thing here to note is that `indexVal` in `key_word` table is the same set of data in `primary_key` in `key_hash` table (I think it can be a foreign key?). In other words, `primary_key` data in `key_hash` table appear as `indexVal` in `key_word` table. But pleas note `indexVal` can appear any number of times inside the table because it is not a primary key in `key_word`.

OK so, this is not the query what I need exactly. I need to count how many times each unique `indexVal` appear in the above search, and divide it by appropriate value in `key_hash.totalNumberOfWords`.

***I am providing few examples below.***

Imagine I ran the above query, now the result is generated. It says

- `indexVal` 0 appeared 10 times in search
- `indexVal` 1 appeared 20 times in search

- `indexVal` 300 appeared 20,000 times in search

Now keep in mind that `key_hash.primary_key` = `key_word.indexVal` . first I search for `key_hash.primary_key` which is similar to `key_word.indexVal` and get the associated `key_hash.numberOfWords`. Then I divide the `count()` appeared in the above mentioned query from this `key_hash.numberOfWords` and multiply the total answer by 100 (to get the value as a percentage). Below is a query I tried but it has errors.


How can I do this job?

**EDIT**

This is how the `key_hash` table looks like




This is how the `key_word` table looks like



+Pie Number of slices to send: Send
Youhan,
Thanks for sharing that it is crossposted. (It is helpful to see what has been said so far.) I made this example work on postgres with some minor syntax changes. The only change I made to the final query was change numberOfWords to totalNumberOfWords to match the column name. It ran without error with that change. Is that your problem? Or is it that the output isn't correct?

Of course, I found a very beautiful couch. Definitely. And this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 665 times.
Similar Threads
autogenerating db-schema cascade=CascadeType.REMOVE but no ON DELETE CASCADE
ManyToOne relation with composite keys
SQL query not giving any result
hibernat creating the tables without cascade
Join query in HQL
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 11:01:46.