SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table1 having nulls then fetch from table2 ?

    Hi all,

    I have two tables with columns as;

    in table1;

    empno,ename,address1,address2,address3,city,state

    in table2;

    empno,ename,address11,address22,address33,city,state

    Now i want to write a query as,

    to display the table1 data with full address,
    if there is null values present in table1 in address columns (i.e all there address1,address2,address3 columns are null) , i want to fetch that row address (from address11,address22,address33 columns) from table2 for that row based on the empno and display at a time.


    Please give me an idea to resolve this problem soon.

    Thanking you.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    95 Post(s)
    Tagged
    0 Thread(s)
    Why are the addresses in different tables when they are presumably about the same entity (presumably employees)?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why did you repost this question? i answered it yesterday

    http://www.sitepoint.com/forums/show...values-present
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why did you repost this question? i answered it yesterday

    http://www.sitepoint.com/forums/show...values-present
    Thank you for your reply.

    But that COALESCE function is checking one by one column.
    i want to check if all the 3 columns are null (but not any one or two), then only i want to go for second-table address columns?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    use COALESCE on the concatenation of the three columns...
    Code:
    SELECT COALESCE(address1||address2||address3, address11) AS address1
         , COALESCE(address1||address2||address3, address22) AS address2
         , COALESCE(address1||address2||address3, address33) AS address3
    ...
    r937.com | rudy.ca | 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
  •