Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help me Query

Author  Topic 

niyaz4872
Starting Member

41 Posts

Posted - 2013-01-10 : 03:13:19
following is my table structure

id count
01 2
04 1
09 5

following is sample results i wan't

id count
01 2
02 0
03 0
04 1
05 0
06 0
07 0
08 0
09 5
10 0

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2013-01-10 : 03:19:36
use Right Join Or LefJoin in your query
could u put your entire query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 03:20:37
does id values come from a master table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

niyaz4872
Starting Member

41 Posts

Posted - 2013-01-10 : 03:26:56
yes .and max id is ten
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 03:31:12
then you can do like

SELECT m.id,COALESCE(t.[count],0) AS [count]
FROM MasterTable m
LEFT JOIN YourTable t
ON t.id = m.id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

niyaz4872
Starting Member

41 Posts

Posted - 2013-01-10 : 03:40:01
tnx.
if id does not values come from a master table?
results i wan't
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 03:40:58
Are you telling that you need to generate id on the fly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

niyaz4872
Starting Member

41 Posts

Posted - 2013-01-10 : 03:43:04
yes
Go to Top of Page

niyaz4872
Starting Member

41 Posts

Posted - 2013-01-10 : 03:44:37
i don't master table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 03:46:22
then how will you decide on upper limit? will it be always 10?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

niyaz4872
Starting Member

41 Posts

Posted - 2013-01-10 : 03:48:16
yes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 03:53:32
[code]
;With NumberTable(N)
AS
(
SELECT 1
UNION ALL
SELECT N + 1
FROM NumberTable
WHERE N + 1 <= 10
)

SELECT n.N,COALESCE(t.[count],0) AS [count]
FROM NUmberTable n
LEFT JOIN YourTable t
ON t.id = n.N
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

niyaz4872
Starting Member

41 Posts

Posted - 2013-01-10 : 04:09:11
tnx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 04:13:06
wlcm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -