sql query problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sql query problem

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-10-30 : 11:44:28

hello,

I want to write an sql query which will also return me empty row of a table.

Here is what I want.

this query returns me 6 results
SELECT [TOCHSLevel],[TOCHSPrefixText],[TOCHSCounterStyleID],[ShowTOCElement] FROM TOCHSElement where TOCHeaderSchemaID=148

And results are as follow
TOCHSLevel TOCHSPrefix TOCHSCounterStyleID ShowTOCElement
1 Bob 87 1
2 Joe 86 1
3 Fred 83 1
4 Sue 81 1
5 Moe 89 1
6 0 1

Now I am writing the same query,but now two tables are involved.

The table TOCNumberingSchemas have only 2 columns TOCHSCounterStyleID and TOCNumberingSchemaName

And this is my query

SELECT e.TOCHSLevel,e.TOCHSPrefixText,t.TOCNumberingSchemaName,e.ShowTOCElement FROM TOCHSElement e,TOCNumberingSchemas t where TOCHeaderSchemaID=148 AND e.TOCHSCounterStyleID=t.TOCHSCounterStyleID;

And here is the result
TOCHSLevel TOCHSPrefix TOCNumberingSchemaName ShowTOCElement
1 Bob test_abc 1
2 Joe Testing 1
3 Fred Roman Numerals 1
4 Sue Lower Case Letters 1
5 Moe Jim's Test 1

I want my output to me like the following in gridview because I will be performing update/delete of them

TOCHSLevel TOCHSPrefix TOCNumberingSchemaName ShowTOCElement
1 Bob test_abc 1
2 Joe Testing 1
3 Fred Roman Numerals 1
4 Sue Lower Case Letters 1
5 Moe Jim's Test 1
6 None 1

Now I can do one thing is to insert NONE with TOCHSCounterSTyleID 0 in the TOCNumberin gSchemas,but TOCHSCounterSTyleID is an identity column and is not allowing me to insert that.

Please help

Thank You

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-30 : 11:47:55

FROM TOCHSElement e
left join TOCNumberingSchemas t on e.TOCHSCounterStyleID=t.TOCHSCounterStyleID

should help

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 12:10:57

also use COALESCE() or ISNULL() to convert NULL values to None

bluestar
Posting Yak Master

133 Posts

Posted - 2008-10-30 : 13:50:36

Hey I am new to this can you tell me how to use these two functions to make it to none. Thanks for your help,Left join solved my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 13:59:05

[code]SELECT e.TOCHSLevel,
e.TOCHSPrefixText,
COALESCE(t.TOCNumberingSchemaName,'None'),
e.ShowTOCElement
FROM TOCHSElement e
LEFT JOIN TOCNumberingSchemas t
ON t.TOCHeaderSchemaID=148
AND e.TOCHSCounterStyleID=t.TOCHSCounterStyleID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources