Query Problem

[mithani]

HI All,

I have two tables:

1. Project Cost Project
2. Project_cost

I want two things:

1. I need all data from Project Cost Project even the field "EXPENSES_CODE" from Project_cost is not availble in Project Cost Project.

2. Only include rows where the joined fields "Project_code" from Project cost project and Project_cost are equal.

When I run query shows error. Please see the attached jpgs.

Please help me.

Mithani

 

[pondlife]

your joins are inconsistent. you could make them both inner joins (option 1), but this might not return the records you want - your para numbered "1" doesn't make sense to me - pls clarify.

 

[mithani]

Thank Pondlife,

You must have found that I am not very stronge with SQL. My table 2 "Project cost" having 100 of cost codes. When we setup a new project, we setup our cost codes which we will use in our entire project. My table 1 "project cost Project" is basically the purchase transcation for different project with different cost codes.

What I want that my sql should display all cost codes from "Project Cost". Than as per project_code and Cost codes, should search purchase from Project cost Project(query) and sum and should show in my report.

Thanks for your help.

Mithani

 

[mithani]

Dear Mate,

Please see the attached excell sheet. Where you can quide me what SQL query has to be written.

Three tables are there:

1. Project .... name of projects
2. Purchase .... purchase transaction
3. Project cost .... having project cost code

4. Need Output.... Thats what I am looking for If need detail of project_Code 1001. I have same codes entered for each projects. If you see the purchase table, only transaction are in Expense_code 50, 100, 150, 120. But my output showing all Expenses_codes from Project Cost table.

How to do that. Please guide me.

Thanks

mithani

 

[ajetrumpet]

Here is another idea for you Mithani...

If you're dealing with a project database, which it looks like you are, I would assume that there is only one project Name, per project, and those names are distinct. Yes? If they are, I wouldn't see any reason for any more than one join; a one-to-many on project name. You would have to include that in your other table though, of course. Just curious, do you have jpg's of both of these tables? I'd be interested in seeing those.

 

[mithani]

Thanks ajetrumpet,

You are right, project names are distinct. See the attached database. Please run the query. I hope you will get better idea that what exactly I am looking for.

Thanks

mithani

 

[mithani]

Hi Friends,

Can anyone help me with my SQL problem.

thanks

mithani

 

[ajetrumpet]

Mithani,

You need to read some articles on normalization and project databases. I'm not sure how your company works, but it looks like you have included some data in the current tables that belong in their own tables. From what you asked, I can't determine what you want, but I can tell you that it might not be possible with the setup you have now. I have redesigned the relationships in the database you posted. It has 2 one-to-many's, and 1 one-to-one relationship.

Read on normalization if you haven't already.

Microsoft's Article, Paul Litwin's Article, The all inclusive Wikipedia