• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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!!!
0
slightlyoff
Asked:
slightlyoff
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Select products.id, products.desc, products.details, brands.BrandName, products.IDBrand  from products left join Brands on products.BrandID = brands.BrandID
0
 
Daniel Van Der WerkenIndependent ConsultantCommented:
You need to do an OUTER JOIN like LEFT OUTER JOIN instead of INNER JOIN for this to work. Inner joins only get the information where the ID's exist in both tables. If you want to have it where the ID doesn't exist in the other table, then you need the OUTER JOIN.

Replace INNER JOIN with LEFT OUTER JOIN and I think you'll get the results you want.
0
 
gpizzutoCommented:
you should use the LEFT OUTER JOIN command:

Select products.id, products.desc, products.details, brands.BrandName, products.IDBrand  from products LEFT OUTER JOIN Brands on brands.BrandID = products.BrandID

Is not a good idea to put the 0 value where there is no Brand. Use instead the NULL value:
better to set up a RELATION between the 2 tables (much safer)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
deightonCommented:
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
0
 
gpizzutoCommented:
If you use 0 value you cannot create a relation between the tables (also in future) ...
0
 
slightlyoffAuthor Commented:
Thank you!  I appreciate the help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now