Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forumdisplay.php?f=24)
-   -   Querying problem. Please help. (http://www.go4expert.com/showthread.php?t=19877)

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 01:29.