• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Joining 2 tables

 
Yohan Weerasinghe
Ranch Hand
Posts: 507
Java Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
**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



 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic