MySQL Statement.

[Khaberstroh]

Good Afternoon. I am not very experiance in writing mysql statements, but I need to have a statement that I can pull data from a database and put it in an excel worksheet. I have already done all of the connecting and pulled some data but now I want to pull it in a differant way.

Heres what I got.
I have 3 tables that all have a column with a unique id called project_seq. These tables contain differant data and I want to bring some columns of data from the tables but not all of the columns and have it matched up with project_seq.

the tables are called.
pmo_project, pmo_progress, pmo_project_period_cost

From pmo_project I want this data.
project_name, project_manager, benefit_org, project_state_name

From pmo_progress I want this data.
currentyear_capital_completion_cost, currentyear_expense_completion_cost, nextyear_capital_completion_cost, nextyear_expense_completion_cost, futureyears_capital_completion_cost, futureyears_expense_completion_cost

From pmo_project_period_cost I want this data
sum(planned_cost) as 'Planned Cost', sum(actual_cost) as 'Actual Cost'

This is what I have so far, but keep getting; ambiguous column name "project_sequence"

mysql4 = "SELECT project_seq, project_name, project_manager, benefit_org, project_state_name, currentyear_capital_completion_cost, currentyear_expense_completion_cost, nextyear_capital_completion_cost, nextyear_expense_completion_cost, futureyears_capital_completion_cost, futureyears_expense_completion_cost, sum(planned_cost) as 'Planned Cost', sum(actual_cost) as 'Actual Cost' FROM pmo_project, pmo_progress, pmo_project_period_cost order by project_seq;"



Any one have some suggestions?

 

[r937]

SELECT P.project_seq
     , P.project_name
     , P.project_manager
     , P.benefit_org
     , P.project_state_name
     , PR.currentyear_capital_completion_cost
     , PR.currentyear_expense_completion_cost
     , PR.nextyear_capital_completion_cost
     , PR.nextyear_expense_completion_cost
     , PR.futureyears_capital_completion_cost
     , PR.futureyears_expense_completion_cost
     , S.pc as 'Planned Cost'
     , S.ac as 'Actual Cost' 
  FROM pmo_project as P
INNER
  JOIN pmo_progress as PR
    ON PR.project_seq = P.project_seq
INNER
  JOIN (
       select project_seq
            , sum(planned_cost) as pc
            , sum(actual_cost) as ac
         from pmo_project_period_cost  
       group
           by project_seq
       ) as S
    ON S.project_seq = P.project_seq

r937.com | rudy.ca

 

[Khaberstroh]

Okay that worked amazingly now I forgot to add a little part to this. On the pmo_progress table and the pmo_project_period_cost table if have a clumn called period which is a date. I want to be able to show only data that has the same project_seq to correspond to a

WHERE period='4/1/2007'

where do I input this statement in the code you provided?
Thanks for all the help!

 

[r937]

well, that depends on a number of things

what is the datatype of the period column?

you realize that if it's an actual DATE column, you won't be able to use date values like '4/1/2007'

r937.com | rudy.ca

 

[Khaberstroh]

it is smalldatetime

 

[Khaberstroh]

Also I have been able to use that WHERE statement above several times with doing this a differant way.

 

[r937]

i have a big surprise for you

it appears you are not running MySQL

this is the MySQL forum



r937.com | rudy.ca

 

[Khaberstroh]

I am writing in VBA, but your statement earlier worked and the data is being pulled from and sql database.

 

[r937]

let me be a bit more explict

SQL = Structured Query Language, i.e. it is a language, not a database system, and it just happens to be the language that most database systems use

SQL Server = microsoft's database system

MySQL = MySQL corporation's database system

SQL Server is not the same thing as MySQL

you cannot slap Honda parts into a Ford engine (well, maybe you can, i don't really know)

you are not using MySQL, and this is the MySQL forum

r937.com | rudy.ca