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.

PHP Code:

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' 


well, that's nice query you have there.

Unfortunately, without knowing your db structure, I cannot help you much, except to tell you you should use OUTER JOIN to do that.

Basically, an INNER JOIN means that if all the tables don't have matching datas, nothing will show up.
But on an OUTER JOIN, the result will display a part of them, even if there are no datas in a second table.

Look at this.

I have a table "user" and a table "loginLog"

create table user(
uid numeric not null,

primary key (uid)
);

create table loginLog(
 uid numeric not null,
 loginDt date,
 success boolean,

 primary key (uid)
);

Now, I'll mach all the user who have registered and logged in.

SELECT *
FROM user u, loginLog l
WHERE u.uid=l.uid

This is valid, but as it's use inner joins, if a member signed up and didn't lgged in, he won't show in the result.

Let's rewrite it with an outer join:

SELECT *
FROM user u LEFT OUTER JOIN loginLog l ON u.uid=l.uid

And there, every users in the table "user" will be displayed, even if there are no record in the table loginLog.

Hope that's help to build your qurey.

NOTE:
Doing an

SELECT *
FROM user u, loginLog l
WHERE u.uid=l.uid

is the same as

SELECT *
FROM user u
INNER JOIN  loginLog l
WHERE u.uid=l.uid