asked on

SQL Inner Join Question

I have an SQL Statement that joins Table A - "Products" with Table B - "Brands" based on a BrandID.

Brand information isn't required. so in many cases, there is no BrandID in the Products Table. The value for BrandID in the Products Table is 0 when no Brand is assigned.

For those products that do have a BrandID, I need to grab the related BrandName . For those that don't have a BrandID, I still need them included in the results.

My statment looks like this:

"Select products.id, products.desc, products.details, brands.BrandName, products.IDBrand from products inner join Brands on brands.BrandID = products.BrandID"

I'm hoping to return all the products in the database, but this will only return those that can be joined with the Brand table. I'm not sure how to work around this.

Any ideas?

Thanks for your help!!!

Kyle Abrahams, PMP

Select products.id, products.desc, products.details, brands.BrandName, products.IDBrand from products left join Brands on products.BrandID = brands.BrandID

ASKER CERTIFIED SOLUTION

Daniel Van Der Werken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Giuseppe Pizzuto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deighton

to give the 0 when there is no brand record

Select products.id, products.desc, products.details, brands.BrandName, COALESCE(products.IDBrand, 0) from products LEFT join Brands on brands.BrandID = products.BrandID

Giuseppe Pizzuto

If you use 0 value you cannot create a relation between the tables (also in future) ...

slightlyoff

ASKER

Thank you! I appreciate the help.