1

Warning: vagueness & unclear questioning will abound because I know squat about databases.

I just discovered that I need to use views as surrogates for a cronned update statement. I can somewhat get the view to work, but I'm having trouble with rows.

This post helped me to bang out the update I need, but now that I know that views can run that update whenever it's needed rather than on a cron schedule, how can I set the view's column value based upon the view's row id or equivalent?

I've got the select I need:

 SELECT SUM( table2.column1/ (

 SELECT table2constant
 FROM table3
 )
 FROM table2
 WHERE table2table1id = table1id

table1id is the AI id column for table1. table2table1id is PKd to table1id. I'd like the view to have a column PKd to table1id like with table2, and the view needs to have every distinct table1id represented.

I'm sure the jargon's way off, but hopefully you can see what I need.

Will provide as many edits as necessary for clarity.

Many thanks in advance!

EDIT1

Should I create a trigger that creates the view upon insert to table1? Just found about materialization which is what I need/want?

Clarity

I need a summed value for each table1.table1id

Progress

With this code, I'm getting the first id from table1 and only the total sum. I need a sum for each table1.id.

CREATE VIEW db1.sums as 

SELECT SUM( table2.column1/ (

 SELECT table2constant
 FROM table3
 ) as theSum, table1id
 FROM table1, table2
 WHERE table2.table2table1id = table1.table1id
Community
  • 1
  • 1
  • So table3 has a single row with a single constant? what is the layout of your tables? I need to understand only the minimal amount of information about what you want but it needs to be a bit clearer. – xception Oct 06 '12 at 19:07
  • yep, table3 is just constant repository. sry for ambiguity, but i have no idea what i'm doing, only in general terms. table1.table1id is AI primary. table2.table2table1id is PKd to table1.table1id. table2.column1 is just a column. table1id is table1.table1id. thank-you very much for your help –  Oct 06 '12 at 19:14
  • 1
    If it's a constant you can just type it in numerically instead, also make the sum first than divide, it should be faster than dividing each term individually. – xception Oct 06 '12 at 19:16

2 Answers2

2

Its actually very simple. Here is an example of how you can do it.

SELECT SUM( table1.column / table2.column ), table1.*, table2.*
FROM table1, table2 
WHERE table1.id = table2.column_id
brenjt
  • 15,997
  • 13
  • 77
  • 118
  • thank-you for trying, but that puts all values into a single field. i need a value for each table1.table1id. I very much appreciate your help & effort. –  Oct 06 '12 at 17:11
  • 1
    @JoeCoderGuy updated my answer. It will pull all columns from both tables – brenjt Oct 06 '12 at 17:30
  • i feel close, but now i'm getting #1060 - Duplicate column name 'id'. Sorry, had to change column names for security. table1 has an id column, and so does table2. thank-you very much for your help –  Oct 06 '12 at 18:43
  • OK, I'm seeing the pattern. I can name the columns, sum1 and id, but I'm fuzzy on how to isolate the sums to each table1.id. The sums should be distinct for each id. Right now, I'm getting a total sum, and only one id. Thank-you very much. –  Oct 06 '12 at 18:55
2

To be clear I'm still not sure what you're trying to accomplish here but if what you posted works, try

SELECT table1.table1id,
    SUM( table2.collumn1 ) / (SELECT table2constant FROM table3 ) as theSum
 FROM table1, table2
 WHERE table2.table2table1id = table1.table1id GROUP BY table1.table1id

you can replace (SELECT table2constant FROM table3 ) with your constant if it has no reason to otherwise be in the database (if it's not updated)

xception
  • 4,241
  • 1
  • 17
  • 27
  • absolutely beautiful! i love this site and all of you. is there a way i can give two checkmarks? brenjt helped me limp closer too. –  Oct 06 '12 at 19:18
  • No, you can only give one up on each answer and a single checkmark on the one that answers your question. Updated the answer a bit, hope it explains some more. – xception Oct 06 '12 at 19:20
  • 1
    I gave brenjt a 1 up on his answer on your behalf :) he was the one who gave you the original idea after all, without him I wouldn't have been able to fix it anyway cause I didn't know what you wanted. – xception Oct 06 '12 at 19:28