JOIN query

[RosieGp]

Hi,
I had the following query to join on 2 tables kwhich worked perfectly fine.

SELECT Customer.Customer.FName, Supplier.*
FROM Customer INNER JOIN Supplier ON Customer.ID = Supplier.ID 
WHERE Supplier.process=True AND Supplier.processDate Is Not Null

Now I want to Join another table Product with supplier in the above query.
Everytime a product is sold I insert the Supplier ID in the product table and the rest of the information in the product table is not in the supplier table.

I changed the above query to the following:

SELECT Customer.Customer.FName, Product.ProductDateSold, Supplier.*
FROM Customer INNER JOIN Supplier ON Customer.ID = Supplier.ID 
JOIN Product ON Supplier.ID = Product.ID
WHERE Supplier.process=True AND Supplier.processDate Is Not Null

The problem is that the above query will only show the data for the ID that is in supplier table and Product table.
How do i change the above query to show me data in supplier table even if the ID does not exist in the product table.
ANY help is appreciated.
Thanks.

 

[denimined]

The second join needs to be a LEFT join. Note that if there is no product data, then ProductDateSold will be NULL. If you want a non-null returned, change the column to something like:
[tt]ISNULL(Product.ProductDateSold, '1900-01-01') as ProductDateSold[/tt]

SELECT Customer.Customer.FName, 
       Product.ProductDateSold, 
       Supplier.*
FROM Customer 
INNER JOIN Supplier 
ON  Customer.ID = Supplier.ID 
[COLOR=#CC0000][b]LEFT[/b][/color] JOIN Product 
ON  Supplier.ID = Product.ID
WHERE Supplier.process      = True 
  AND Supplier.processDate Is Not Null

 

[RosieGp]

Thanks Denimined.

How can i do the following:
ISNULL(Product.ProductDateSold, '1900-01-01') as ProductDateSold

within the query.

Thanks.

 

[denimined]

SELECT Customer.Customer.FName, 
      ISNULL(Product.ProductDateSold, '1900-01-01') as ProductDateSold, 
       Supplier.*
FROM Customer 
INNER JOIN Supplier 
ON  Customer.ID = Supplier.ID 
LEFT JOIN Product 
ON  Supplier.ID = Product.ID
WHERE Supplier.process      = True 
  AND Supplier.processDate Is Not Null

 

[Olaf Doschke]

The ID field of records is one side of a join, but the other side should be a foreign key. You don't join Customer.ID = Supplier.ID, you either join Customer.ID = Supplier.CustomerID or Customer.SupplierID = Supplier.ID

In the same way you join Product ON Supplier.ID = Product.SupplierID

If you join Customer.ID = Supplier.ID you just by chance get a correct join, as two records have the same ID value, but their relation is not done via both primary ID values, unless the IDs have the same meaning of pointing to companies, in this case, that means you can do a join of two tables by a foreign key they both share. But you don't name foreign keys "ID".

I don't know how you relate your tables in case of customers and suppliers, both can be companies, but supplier IDs surely don't relate to product IDs. I assume both ID fields are integers, but supplier 1 does not only produce or deliver product 1, supplier 2 does not only produce or supply product 2 and so on, do they? The product table has to have a field pointing to the supplier or there has to be a secondary cross reference table relating products and suppliers, eg a certain product of course could be supplied by many different suppliers, therefore you most certainly will not find a supplierID inside the Product Table, but you should find a table ProductSuppliers or SupplierProducts or ProductsOfSuppliers, SuppliersOfProducts, something similar to that, which would contain pairs of productIDs and SupplierIDs.

A healthy database design would be

Company: ID, Name, ... other fields
Customer: ID, CompanyID, ... other fields
Supplier: ID, CompanyID, ... other fields
Product: ID, Name, UPCCode, AveragePrice, ... other fields

ProductSupplier: ID, ProductID, SupplierID, Price, ... other fields
CustomerSupplier: ID, CustomerID, SupplierID,...

Then your SQLs would join Company and Supplier or Supplier and Prodcut via the last two tables, there is no direct relation between the upper 4 tables, surely not by the IDs. A Customer can have many suppliers, a supplier many customers, a proidcut can be sold by many suppliers etc etc.

Just get your database design right and everything falls into place.

Bye, Olaf.