SQL Display Null Values

[NiteshR]

Hi,
I am using C# code to retreive values from a table in sequel.
This is done using a join between a table containg values and one using dates.
The problem is that I am unable to retrieve a value for days where there is no value. I would like to return a 'NULL' value or even a ' 0 ' value if no amount is found for that specific date/day.


SELECT COUNT(*) AS Expr1
FROM db.sales_month RIGHT OUTER JOIN
db.sales_dates ON dbo.sales_month.full_date = db.sales_dates.[Date] AND db.sales_month.full_date = db.sales_dates.[Date]
WHERE (db.sales_month.prod_name = 'Chairs') AND
GROUP BY db.sales_month.full_date
ORDER BY db.sales_month.full_date

[scripto]

SELECT COUNT(*) AS Expr1
FROM db.sales_month RIGHT OUTER JOIN
db.sales_dates ON (dbo.sales_month.full_date = db.sales_dates.[Date] AND
db.sales_month.prod_name = 'Chairs')
GROUP BY db.sales_month.full_date
ORDER BY db.sales_month.full_date

[NiteshR]

SELECT COUNT(*) AS Expr1
FROM db.sales_month RIGHT OUTER JOIN
db.sales_dates ON (dbo.sales_month.full_date = db.sales_dates.[Date] AND
db.sales_month.prod_name = 'Chairs')
GROUP BY db.sales_month.full_date
ORDER BY db.sales_month.full_date

Thanks for the help.
I used the code you sent me, but it still doesn't output all the null values.
The only output are those which are greater than 0.
Is there anything else i can use to do this? eg. a if..then statement in sql

[scripto]

if there is no entry for a specific day or days in the db.sales_dates table then you will not get a null value or a 0. You would need to create another join on another table (like tblSeptember) where those dates exist, then you can get all the nulls for dates in September.

[NiteshR]

if there is no entry for a specific day or days in the db.sales_dates table then you will not get a null value or a 0. You would need to create another join on another table (like tblSeptember) where those dates exist, then you can get all the nulls for dates in September.

Ok, I understand what you mean by this.
The only way i think i can overcome this is by inserting a if..else statement within the sql query, maybe by creating a stored procedure.

Any ideas on this?

Thanks.

[scripto]

well, if your db.sales_month table has the dates you need, then try a Left Outer join. Let me/us know how it goes.

[NiteshR]

well, if your db.sales_month table has the dates you need, then try a Left Outer join. Let me/us know how it goes.

hi,
i joined the two tables using a right outer join and insert those values into a new table.
i then joined that table using a right outer join to give me the output i required.

thanks.