• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
  • Mikalai Zaikin

Joining 2 tables

Ranch Hand
Posts: 507
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?


This is how the `key_hash` table looks like

This is how the `key_word` table looks like

author & internet detective
Posts: 41855
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?

With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic