SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join table if null

    Hi I have 2 tables joining using left join:

    PHP Code:
    SELECT e.*, re.* FROM event e
    LEFT OUTER JOIN event_recur re ON
    (e.eventID re.eventID
    Since both table consist of the same column name e.eventID = re.eventID, if event_recur doesn't have any record, my main e.eventID will be "overwritten" as NULL. For example:

    PHP Code:
    +----------+--------------+----------+
    eventID  |   title      |  eventID |  <--- this is from event_recur
    +----------+--------------+----------+
    |   
    112    |   testing    |   NULL   |
    +----------+--------------+----------+ 
    Is there any better way to prevent this instead of manually typing out all the individual columns?

    Thanks in advanced for any advise.
    I Dunno LOL \(_o)/

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe switch the e.* and re.* around?
    still, though laziness is a virtue, I think explicitly naming your columns is the best way.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    ... my main e.eventID will be "overwritten" as NULL.
    no, it won't

    your result set will have two columns called "eventID" and it is your application language that cannot distinguish between them

    the best way around this is to assign column aliases, e.g.
    Code:
    SELECT e.name AS e_name
         , re.name AS re_name
         , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes Rudy in my language can't distinguish them. Can't use alias as my code has already been populated with "eventID" name everywhere.

    I think I shall go with naming the columns out individually in the sql then. Thank jurn and Rudy.
    I Dunno LOL \(_o)/

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    Can't use alias as my code has already been populated with "eventID" name everywhere.
    that would be a rename, which is not what i meant


    Quote Originally Posted by justspree View Post
    I think I shall go with naming the columns out individually in the sql then.
    assign column aliases there
    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
  •