SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    Hiding from the world
    Posts
    357
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    having trouble Joining tables for the first time

    Hi, my poor brain is struggling to understand how to get my tables to join and get it to give me the information i want. This is the first time ive tried to make a relational table structure.

    I have three tables, each has an organiser id that should join them together, the trouble starts when i want to select a specific id and just output the records associated with that id.
    if i do this
    Code:
    $id=$_GET['id'];
    
    $query_Recordset1 = "SELECT DISTINCT name,contact_name, orgID FROM event, beachData, passwords
    WHERE org_id='$id'  AND beachData.orgID=passwords.org_id ";
    i get the correct results but they are duplicated.

    if i do this
    Code:
    $query_Recordset1 = "SELECT DISTINCT beachName,contact_name, BWOrgID FROM events, beachData, passwords 
    WHERE org_id='$id' AND events.orgID=beachData.orgID AND beachData.orgID=passwords.org_id ";
    If works fine unless the events table (for example) has no event and subsequently no org_id and then it just doesn't display any information.

    Iam i going about this all wrong or is there a simple way to get it to work. any help would be great.

  2. #2
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well it would help a lot of we could see your tables.. however, if you say the second query works except that it doesnt display the data from the fisrt table unless its in the second table, a left join is what you need

    This might be what you are after.

    $query_Recordset1 = "SELECT DISTINCT beachName
    ,contact_name
    , BWOrgID
    FROM events
    LEFT JOIN beachData ON
    events.orgID=beachData.orgID
    AND beachData.orgID=passwords.org_id";

    edit.. i changed the above 3 times.. i think that might be right now.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    Hiding from the world
    Posts
    357
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for pointing me in the right direction. It didn't do what i wanted to start with so i read up some more on left joins and eventually worked out how it should be for my tables as i found i needed WHERE clauses to select specific records. It certainly helped being shown how to go about joining the tables correctly. Thank you and sorry the reply took so long.

  4. #4
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no problem, glad to know you've figured it out.


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
  •