Join table with empty data

Discussion in 'Databases' started by cesarcesar, Jan 7, 2007.

  1. #1
    Hello,

    I'm a novice, and am having issues get a JOIN to work properly. The following code is correct for pulling field values that HAVE a value, but when a field i am requesting has a value like Null, Space, or just empty, the whole query comes back empty.

    It was suggested i use TRIM in my call, but i'm not exactly sure how to use properly.

    As far as each table, the fields that are empty are set to ALLOW NULL. Thanks for the help.

    
    Select
    company.company_name,
    company.company_username,
    company.company_clients_served,
    company.company_status,
    note.note_text,
    address.address_street1,
    address.address_street2,
    address.address_zip,
    address.address_zip_ext,
    county.county_id,
    city.city_name,
    taxes_states.state_id,
    email.email_address,
    url.url_path,
    url.url_name,
    company_type.company_type_id
    From
    company
    Inner Join note ON company.note_id = note.note_id
    ,
    address
    Inner Join connector_address ON company.company_id = connector_address.ref_id AND connector_address.address_id = address.address_id
    Inner Join county ON address.county_id = county.county_id
    Inner Join city ON address.city_id = city.city_id
    Inner Join taxes_states ON address.state_id = taxes_states.state_id
    Inner Join connector_email ON company.company_id = connector_email.ref_id
    Inner Join email ON connector_email.email_id = email.email_id
    Inner Join connector_url ON company.company_id = connector_url.ref_id
    Inner Join url ON connector_url.url_id = url.url_id
    ,
    company_type
    Inner Join connector_company ON company_type.company_type_id = connector_company.company_type AND company.company_id = connector_company.company_id
    Where company.company_id = '15'
    
    PHP:
     
    cesarcesar, Jan 7, 2007 IP
  2. smallbuzz

    smallbuzz Peon

    Messages:
    125
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Instead of using Inner join, you need to use a left, right, or full outter join. Google them.
     
    smallbuzz, Jan 9, 2007 IP