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

    Join Date
    Feb 2008
    Posts
    74
    Rep Power
    7

    Handling NULL values


    I have 2 tables, AFF_TEMP and COUNTY
    AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY
    COUNTY has 2 columns COUNTY_ID and CNAME

    Both tables have the following test data
    AFF_TEMP
    Joe, Bloggs, joe@gmail.com, ''
    Ann, Bloggs, anne@gmail.com,Donegal

    and COUNTY column in AFF_TEMP can contain a NULL value

    County table has the following Test data,
    1, Dublin
    2, Donegal
    3, Tipperary,
    4, Galway


    I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.
    Tried the following queries
    select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME
    OR (A.COUNTY IS NULL)

    select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C
    WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)
    or A.COUNTY IS null

    but cant get it to work....
    This should be my output
    Joe, Bloggs, joe@gmail.com, null
    Ann, Bloggs, anne@gmail.com,6

    Appreciate any help on this!
    Last edited by fee1975; January 23rd, 2013 at 10:18 AM.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Post the results of these queries:
    Code:
    select *
     from  temp_aff
    
    select *
     from  county
    
    select a.fname, a.lname, a.email, nvl( c.county_id, '** No Match found' ) as county_id
     from  temp_aff a left outer join county c on a.county  =  c.cname
    
    select a.fname, a.lname, a.email, nvl( c.county_id, '** No Match found' ) as county_id
     from  temp_aff a left outer join county c on trim( lower( a.county ) )  =  trim( lower( c.cname ) )
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Posts
    74
    Rep Power
    7
    Results from first Query
    Code:
    Corcoran	Sean	sean@dit.ie 	095-742842 HP			
    Fallon	 Declan	declan.fallon@hotmail.co.uk	091-742842	HP			
    Douglas	Lorna	lorna.douglas@gmail.com	091-787904				Limerick
    Results from second query
    Code:
    1	TIPPERARY
    2	LOUTH
    3	CLARE
    4	DUBLIN
    5	GALWAY
    6	LIMERICK
    Results form 3rd query
    Code:
    Declan	Fallon Declan.fallon@hotmail.co.uk	** No Match found
    Sean	Corcoran	sean@dit.ie	** No Match found
    Lorna	Douglas	lorna.douglas@gmail.com	** No Match found
    And 4th query results
    Code:
    Lorna	Douglas	lorna.douglas@gmail.com	6
    Declan	Fallon	declan.fallon@hotmail.co.uk	** No Match found
    Sean	Corcoran	sean@dit.ie	** No Match found
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Posts
    74
    Rep Power
    7
    Got it working thanks

IMN logo majestic logo threadwatch logo seochat tools logo