Querying problem. Please help.

Assi9's Avatar, Join Date: Aug 2009
Go4Expert Member
Hi friends. I am supposed to display the Project_Name of all rows in the Project Table which do not form part of the Completed_Project table. It should return Feasibility Study RE (i.e. Project_ID = 4) and Website Development RE (i.e. Project_ID = 5). Please please help.

Table: Project

Project_ID=== Project_Name
1=========Business Plan
2 ======== Software Design
3 ======== Schedule program
4======== Feasibility Study RE
5 ======== Website Development RE

Table: Completed Project

Project_ID (FK) === Outcome
1 ============Success
2 ============ Success
3 ============Success

Last edited by Assi9; 24Oct2009 at 21:11..
0
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
What do you want as output? this?

Code:
Project ID Project Name           Outcome
1          Business Plan          Success
2          Software Design        Success
3          Schedule Program       Success
4          Feasibility Study RE            
5          Website Development RE
If yes, then what you are looking for is called Left Outer Join

and here is the query:
Code: sql
SELECT Project.*
     , Completed_Project.Outcome
  FROM Project LEFT JOIN Completed_Project
    ON Project.Project_ID = Completed_Project.Project_ID;

Last edited by nimesh; 24Oct2009 at 23:22.. Reason: modified code block
Assi9 like this
0
Assi9's Avatar, Join Date: Aug 2009
Go4Expert Member
Hi again, Nimesh. Thanks a million for the reply.

The output that I want is this:
Code:
Project_ID          Project_Name
4                     Feasibility Study RE            
5                     Website Development RE
You see, the Project Table stores info about all the projects, even those not completed. I am required to find a way to display the names of all the projects which are yet to be completed. Is it possible? I've tried half a dozen approaches, and all have failed miserably.
0
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
Yes you can get that,

Code: SQL
SELECT Project.*
  FROM Project LEFT OUTER JOIN Completed_Project
    ON Project.Project_ID = Completed_Project.Project_ID
 WHERE Completed_Project.Outcome IS NULL;
Assi9 like this
0
Assi9's Avatar, Join Date: Aug 2009
Go4Expert Member
Wow! That hit the spot! Thanks a lot, mate! You are simply brilliant!
nimesh like this