SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot dereko's Avatar
    Join Date
    Dec 2003
    Location
    Ireland
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditional Joins?

    Here is a simplistic version of what I'm trying to do:

    I have 2 tables :

    PROJECT and PROJECT_CONTACT

    PROJECT ---
    id int(11) PRIMARY
    name varchar(50)
    desc varchar(255)
    etc...

    PROJECT_CONTACT ---
    id int(11) PRIMARY
    project_id int(11)
    name varchar(50)
    address varchar(255)
    etc ..


    now i have a sql statment

    Code:
    SELECT project.name AS pname, project_contact.name AS pcontact
    FROM project, project_contact
    WHERE (project.project_id = project.id) 
    AND project.id = 1;
    Now the thing is this works fine if there is always a project contact for every project but in some cases I have no project contact in which case this whole sql will return no data. I need it to still return the data in the project table, is there any way to work this in SQL?

    Cheers,
    Derek

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, what you have written above is an inner join, turn this into an outer join and you will get the results you want:

    Code:
     SELECT project.name AS pname, project_contact.name AS pcontact
    FROM project
    left outer join
     project_contact
    ON project.project_id = project.id
    AND project.id = 1;

  3. #3
    SitePoint Zealot dereko's Avatar
    Join Date
    Dec 2003
    Location
    Ireland
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks daveman, should have figured that.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •