#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    111
    Rep Power
    4

    Join tables and getting records even no data on other table?


    Hi. I know how to do a left join but let's say I've a table A and table B.

    They have member_id field on both table then let's say I've education on table A and then member's interest.

    But sometime some members will have education and no interest or have interest but no education info on the tables.

    So doing a left join A to B or B to A then I will sometime missed some records.

    I don't want to use the master table members then left join table A and left join B because members table is too big. Please answer my question instead of saying something else, etc.

    Is there a way to join table A and B or other way so I can get the records of A and B even there may not be data on either one for some members?

    Hope you know what I mean and can help me. Thank you.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    what you want is a FULL OUTER JOIN, but unfortunately, mysql doesn't support it

    so you will have to do a LEFT OUTER JOIN with a UNION (not UNION ALL) of a RIGHT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I was going to say something else, but wasn't allowed.

IMN logo majestic logo threadwatch logo seochat tools logo