SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2002
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting records from multiple tables

    Hi,

    I have been trying to write a mySQL query to retrieve records from two or more tables. Each table has a field for groupID. I want to be able to select certain fields from each table and return them as a single record.

    This is what I have so far:


    Code:
    SELECT affiliation.groupID,affiliation.group_name,church_gate.groups,church_gate.churches,e_matters.website_address,e_matters.website_password FROM affiliation,church_gate,e_matters WHERE affiliation.groupID=church_gate.groupID OR affiliation.groupID=e_matters.groupID OR affiliation.groupID='Clenorcon';


    This works as long as there is an entry for that groupID in every table. However, if one of the tables does not have an entry, nothing is returned.

    How can I change this script so that it returns the values in the other tables which do have entries and just ignores the on without entries?

    Thanks in advance,

    Shane McHale

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sounds to me like you are asking for a left outer join
    Code:
    select affiliation.groupID
         , affiliation.group_name
         , church_gate.groups
         , church_gate.churches
         , e_matters.website_address
         , e_matters.website_password 
      from affiliation
    left outer
      join church_gate
        on affiliation.groupID=church_gate.groupID 
    left outer
      join e_matters 
        on affiliation.groupID=e_matters.groupID 
     where affiliation.groupID='Clenorcon'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •