Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forums/database-forum/)
-   -   Querying problem. Please help. (http://www.go4expert.com/forums/querying-help-t19877/)

Assi9 24Oct2009 20:53

Querying problem. Please help.
 
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

nimesh 24Oct2009 23:21

Re: Querying problem. Please help.
 
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;


Assi9 24Oct2009 23:31

Re: Querying problem. Please help.
 
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.

nimesh 24Oct2009 23:45

Re: Querying problem. Please help.
 
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 25Oct2009 11:54

Re: Querying problem. Please help.
 
Wow! That hit the spot! Thanks a lot, mate! You are simply brilliant!


All times are GMT +5.5. The time now is 05:40.