SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query multiple tables that may be linked or may not be

    I have a table called mytable, and two other tables called option1 and option2
    here is their structure:
    mytable
    id, name

    option1
    id, mytable_id, name

    option2
    id, mytable_id, name

    Some, but not all, entries for mytable will be linked to option1 and option2.

    I would like to have one query that selects all the rows from mytable, and if there is an option1 entry for that mytable row, grab it as well, and the same for option2

    The only way i know how to do this now is to run a query for all the mytable rows and then in a php while loop, query to see if there are any option1 or option2 entries that are linked to mytable.

    The main crux of this thing is that not every mytable entry is linked to an option1 or option2

    thanks for any help!

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Code SQL:
    SELECT
            m.id
            ,m.name
            ,o1.id AS options_1_id
            ,o1.name AS options_1_name
            ,o2.id AS options_2_id
            ,o2.name AS options_2_name
      FROM
            mytable m
       LEFT
       JOIN
            option1 o1
         ON
           m.id = o1.mytable_id
      LEFT
      JOIN
          option2 o2
        ON
          m.id = o2.mytable_id

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works great! I never knew you could give your tables variables like o2, m etc. that is really cool thanks so much for sharing!

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's not a variable, it's an alias.
    Call it by another name to shorten the code you write and make it easier to follow.
    It's databases 101

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, i obviously missed that class!


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
  •