• 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 ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

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?

**EDIT**

This is how the `key_hash` table looks like




This is how the `key_word` table looks like



 
author & internet detective
Posts: 42074
932
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
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?

 
Did you ever grow anything in the garden of your mind? - Fred Rogers. Tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic