1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Querying problem. Please help.

Discussion in 'Database' started by Assi9, Oct 24, 2009.

  1. Assi9

    Assi9 New 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: Oct 24, 2009
  2. nimesh

    nimesh New Member

    What do you want as output? this?

    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:
    SELECT Project.*
         , Completed_Project.Outcome
      FROM Project left JOIN Completed_Project 
        ON Project.Project_ID = Completed_Project.Project_ID;
    Last edited: Oct 24, 2009
  3. Assi9

    Assi9 New Member

    Hi again, Nimesh. Thanks a million for the reply.

    The output that I want is this:
    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.
  4. nimesh

    nimesh New Member

    Yes you can get that,

    SELECT Project.*
      FROM Project LEFT OUTER JOIN Completed_Project 
        ON Project.Project_ID = Completed_Project.Project_ID
     where Completed_Project.Outcome is null;
  5. Assi9

    Assi9 New Member

    Wow! That hit the spot! Thanks a lot, mate! You are simply brilliant!

Share This Page