How to display all from one table even if it does not exist in the other

[dan555]

Dear all programmers

I’ve search all over Google and have not found an answer.

My question is.........

I have two DBF tables called product and ullage. The user enters data from products table into ullage, by searching for product id and adds their count into the ullage table. This part is working

The question is how can I display the products table with all the prod_ID, Desc and price with Counts from the ullage table but were there is no count in the ullage table these product will be displayed but with no count number.

The Tables....

Product table
Prod_ID | Desc |price
1 Food 5.99
2 Drink 3.99
3 Laptop 50.49
4 Desktop 100

Ullage table
Prod_ID | Count
1 1
2 -3

Result ...........

Prod_ID | Desc |price|Count
1 Food 5.99 1
2 Drink 3.99 -3
3 Laptop 50.49
4 Desktop 100

[hericles]

Use coalesce function of the ANSI standard.It will insert the value of count unless it is null, in which case it will insert your specified default value.

SELECT p.prod_id, desc, price, coalesce(count, 0) FROM product as p LEFT JOIN uiiage ON p.prod_id = uiiage.prod_id;

The LEFT JOIN maes sure you are including all rows from the table on the left (product) and joining uiiage to it where you can, which would leave NULLs where there was no match.

Hope that helps,

[adam_k]

@hericles: Agree, but with a correction:

SELECT p.prod_id, desc, price, coalesce(count(uiiage.*), 0) FROM product as p LEFT JOIN uiiage ON p.prod_id = uiiage.prod_id;

PS: This assumes that your db is SQL

[dan555]

Thank you for your respond

I have implemented your code using the query builder in visual studio 2010. I am get a result but also an error next to Desc.

SELECT p.code, price, DESC, count
FROM product AS p LEFT JOIN
uiiage ON p.code = uiiage.code

coalesce(count, 0) was not recognized

Thanks

Dan

Use coalesce function of the ANSI standard.It will insert the value of count unless it is null, in which case it will insert your specified default value.

SELECT p.prod_id, desc, price, coalesce(count, 0) FROM product as p LEFT JOIN uiiage ON p.prod_id = uiiage.prod_id;

The LEFT JOIN maes sure you are including all rows from the table on the left (product) and joining uiiage to it where you can, which would leave NULLs where there was no match.

Hope that helps,

[adam_k]

change coalesce(count,0) to isnull(count(uiiage.*), 0)