Return an extra row - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Return an extra row

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-11-10 : 03:36:31

Hi, I've got a table of items, and the items can have a category.
So, I also have a table with categories.
Now, I'm doing this:

SELECT
[CategoryID],
[CategoryName],
[IconID],
NumberOfItems = (select count(ItemCategory) from Items where ItemCategory = [CategoryID])
FROM
[Categories]

But not all items have a category (I actually insert a -1 in the ItemCategory column)

So I would also like to return a 'dummy' category for the items which do not have an actual category. It should look something like this:

CategoryID = -1
Categoryname = 'None'
IconID = -1
NumberOfItems = 123

So how would I do this? Perhaps some 'right join' on the items table would be better?

The secret to creativity is knowing how to hide your sources. (Einstein)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 03:40:22

You can insert your dummy into the Categories table.
And you should make the Items.ItemCategory a foreign key.

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

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-11-10 : 04:08:54

Yes, I could have done that, but that was not the question...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 04:17:14

[code]SELECT
[CategoryID],
[CategoryName],
[IconID],
NumberOfItems = (select count(ItemCategory) from Items where ItemCategory = [CategoryID])
FROM
(
SELECT
[CategoryID],
[CategoryName],
[IconID]
FROM [Categories]
UNION ALL
SELECT -1, 'None', -1
) AS [Categories]

[/code]

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

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 05:20:44

I think this is what the OP needs


SELECT
[CategoryID],
[CategoryName],
[IconID],
NumberOfItems = (select count(ItemCategory) from Items where ItemCategory = [CategoryID])
FROM 
[Categories]
UNION ALL
SELECT -1, 'None', -1,(select count(ItemCategory) from Items where ItemCategory = [CategoryID])

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 05:44:18

quote:

Originally posted by Sachin.Nand

I think this is what the OP needs


SELECT
[CategoryID],
[CategoryName],
[IconID],
NumberOfItems = (select count(ItemCategory) from Items where ItemCategory = [CategoryID])
FROM 
[Categories]
UNION ALL
SELECT -1, 'None', -1,(select count(ItemCategory) from Items where ItemCategory = [CategoryID]-1)

PBUH

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 05:45:46

My solution does the same thing without coding an extra subselect.

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

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 06:12:01

quote:
So I would also like to return a 'dummy' category for the items which do not have an actual category. It should look something like this:
CategoryID = -1
Categoryname = 'None'
IconID = -1
NumberOfItems = 123

OP needs NumberOfItems = 123 also in the dummy record.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 07:02:57

I don't think so.
I believe the 123 was just an example.

Let's wait what the OP is telling us if he comes back...

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

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-11-10 : 14:37:45

123 is an example (sorry)...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-10 : 15:22:51

I realize this doesn’t really have anything to do with your question, but how do you maintain referential integrity of you don't have foreign keys between your tables?

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-10 : 15:27:28

quote:
Originally posted by Lamprey

I realize this doesn’t really have anything to do with your question, but how do you maintain referential integrity of you don't have foreign keys between your tables?

Spit and duct tape.
duh.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-11 : 13:07:16

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Apparently, you have no idea how silly the concept of a "category_id" is in a data model, nor how wrong changing data elements names from table to table is. Let's fix the basics:

CREATE TABLE ShopCategories
(shop_cat INTEGER NOT NULL PRIMARY KEY,
shop_cat_description VARCHAR(100) NOT NULL);

CREATE TABLE Items -- can you be more vague?
( item_id CHAR(15) NOT NULL PRIMARY KEY,
...
shop_cat INTEGER DEFAULT -1 NOT NULL
REFERENCES ShopCategories (shop_cat)
ON UPDATE CASCADE,
..);

Doing an encoding with integers is really bad design, but I will skip over this design flaw and keep going. Is this your query, with proper names?

SELECT C.shop_cat, C.shop_cat_description, I.item_id,
(SELECT COUNT(Items.shop_cat)
FROM Items AS I
WHERE Items.shop_cat = C.shop_cat) AS shop_cat_cnt
FROM ShopCategories AS C)

Do you know that the scalar subquery will be done over and over?

>> But not all items have a category (I actually insert -1 in the Items.shop_cat column) <<

Okay, look at the DDL and see if that does what you said.

>> So I would also like to return a 'dummy' category for the items which do not have an actual category. <<

So what is this -1 thing? Where did "icon_id" come from? I assumed you meant "item_id", but that was another wild guess. If you want help, please be clear.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources