Databases
StackOverflow2013
Postsdbo
POmySQL Query count, select three tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmySQL Query count, select three tables
    primarykey
    data
    text
    <p>Hope I can get some help with this problem. I have three tables, one has posts (ideas) called op_ideas. I have another table which is called op_idea_votes. These can be linked by idea_id.</p> <p>when an idea is posted, folk can vote on the idea. op_idea_votes is then updated with the latest vote.</p> <p>I then have another table called op_organs, which may or may not be empty depending on what user type posted. So this table is linked by POST_TYPE (which is the id for op_organs table) to op_organs.organ_id.</p> <p>I need to select all these tables to create a row with all the info to display, which need to be linked by idea_id, and post_type. But I also need COUNT on 3 of the columns on op_idea_vote table (agree, disagree, abstain).</p> <p>I have tired UNION, SELECT with SUM(CASE, but I cannot get the desired affect. LEFT and INNER JOIN's but I seem to get something wrong, as part or all of the info is missing.</p> <p>The app is in javascript and PHP, so I am trying to lessen the number of calls to the database and to the PHP file (with ajax) where the query is, which is why I am trying to do it one go. Thou if there is a better way I am all ears.</p> <p>This is the current attempt created from a tool, but it comes back empty, but no error:</p> <pre><code>SELECT op_ideas.author_id, op_ideas.date, op_ideas.deptid, op_ideas.idea_geo, op_ideas.idea_id, op_ideas.idea_text, op_ideas.idea_title, op_ideas.modifed_date, op_ideas.post_type, op_ideas.user_country, op_ideas.user_county, op_ideas.user_loc, op_organs.organ_name, op_organs.organ_logo, op_organs.active, COUNT(op_idea_vote.agree), COUNT(op_idea_vote.disagree), COUNT(op_idea_vote.obstain) FROM heroku_056eb661631f253.op_ideas op_ideas, heroku_056eb661631f253.op_organs op_organs, heroku_056eb661631f253.op_idea_vote op_idea_vote WHERE op_ideas.idea_id = op_idea_vote.idea_id AND op_ideas.post_type = op_organs.organs_id </code></pre> <p>So the data would look like this:</p> <p>+--------------------------------------------------------------------------------------------------------------------------------+<br> | idea_id | author_id | idea_title | idea_text | date | agree | disagree | abstain | organ_name | organ_logo<br> +--------------------------------------------------------------------------------------------------------------------------------+<br> | 00001 | 10025225 | teste title | text here | 2012| 00005 | 0000004 | 000002 | Comp Name | image/.png<br> +--------------------------------------------------------------------------------------------------------------------------------+ </p> <p>Not a great example. But I tried.</p> <p>Here is the latest code. It brings back the 3 rows, even if op_organs is null. But it does not show the votes count:</p> <pre><code> SELECT i.*, o.organ_name, o.organ_logo FROM heroku_056eb661631f253.op_ideas i LEFT JOIN op_organs o ON i.post_type = o.organs_id JOIN (SELECT COUNT(v.agree = 1 or null) as agree, COUNT(v.disagree = 1 or null) as disagree, COUNT(v.obstain = 1 or null) as abstain FROM op_idea_vote v, op_ideas i GROUP BY v.idea_id ) as votes; </code></pre> <p>Spent all day on this, read so much my head is spinning. Anyone have any ideas ?</p> <p>Thanks</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrew Walker
    1. USAndrew Walker
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIt's not clear exactly what output you desire. Perhaps some example data and the desired output would help?
      singulars
      1. POmySQL Query count, select three tables
      1. USeggyal
    2. COI need a row containing the fields above. But note, op_organs could be null, and I need to count the fields in op_idea_vote based on each record in op_ideas. I will edit the top and try and visualise it.
      singulars
      1. POmySQL Query count, select three tables
      1. USAndrew Walker
    3. COwhen you use count, select will be always 1 row. So you should try to select all data into one row see GROUP_CONCAT
      singulars
      1. POmySQL Query count, select three tables
      1. USZaffy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload