How to assign a text value to replace a text value in the result set of a query

I am running a query to show all items that have either not sold or sold less than 3 times with a database that has the Sale, Iineitem (as in receipt information, and item data in 3 separate tables joined via a 3 table join. I could only get this to work using a UNION and had to add in a third column as a place hold for the count for items that have not sold at all. The query is as such:

SELECT lineitem.itemno, item.itemname,COUNT(lineitem.itemno)
FROM item, lineitem
      WHERE item.itemno = lineitem.itemno
         GROUP BY lineitem.itemno, item.itemname
            ORDER BY COUNT(lineitem.itemno);

UNION


SELECT item.itemno, item.itemname, itemcolor
   FROM item
      WHERE (((item.itemno) Not In (SELECT lineitem.itemno FROm lineitem)))


The result set looks as such:
itemno      itemname      Count Sold
1      Pocket knife - Nile      Brown
2      Pocket knife - Avon      5
3      Compass      1
4      Geo positioning system      2
5      Map measure      -
6      Hat - Polar explorer      1
7      Hat - Polar explorer      White
8      Boots - snake proof      Green
9      Boots - snake proof      1
10      Safari chair      1
11      Hammock      1
12      Tent - 8 person      1
13      Tent - 2 person      2
14      Safari cooking kit      1
15      Pith helmet      Khaki
16      Pith helmet      1
17      Map case      Brown
18      Sextant      -
19      Stetson      1
20      Stetson      Brown

Like I said, I was only able to get the UNION to work by adding in the itemcolor on the second query to match the 3 columns needed for the first column. Now I want to chang the value displayed for the itemcolor value, which is text to display as '0' to indicate not sold. How do I set '0' to display as opposed to the actual itemcolor?
Would I use an if then? if so can someone please provide the SQL syntax for that.
chonabrahamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chonabrahamAuthor Commented:
I need to correct the query text:

SELECT lineitem.itemno, item.itemname,COUNT(lineitem.itemno) AS [Count Sold]
FROM item, lineitem
      WHERE item.itemno = lineitem.itemno
         GROUP BY lineitem.itemno, item.itemname
     
              Having  COUNT(lineitem.itemno) <3
               

UNION


SELECT item.itemno, item.itemname, itemcolor
   FROM item
      WHERE (((item.itemno) Not In (SELECT lineitem.itemno FROm lineitem)))
0
Saurabh Singh TeotiaCommented:
You can just use simply...

SELECT lineitem.itemno, item.itemname,COUNT(lineitem.itemno) AS [Count Sold]
FROM item, lineitem
      WHERE item.itemno = lineitem.itemno
         GROUP BY lineitem.itemno, item.itemname
     
              Having  COUNT(lineitem.itemno) <3
               

UNION


SELECT item.itemno, item.itemname, 0 as item_color
   FROM item
      WHERE (((item.itemno) Not In (SELECT lineitem.itemno FROm lineitem))) 

Open in new window

0
chonabrahamAuthor Commented:
Great! Simple solutions. Thank you!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chonabrahamAuthor Commented:
Can I order by the count with the itemcolor as a text field?
0
chonabrahamAuthor Commented:
I'd like to get the 0 sold items listed first.
0
Saurabh Singh TeotiaCommented:
I don't understand what you mean by this..

Can I order by the count with the itemcolor as a text field?

Can you help me understand what you want to do??
0
chonabrahamAuthor Commented:
I want to show all of the 0 counts first in the result set then the 1, 2, 3 for but since the itemcolor is text, the Order By clause isn't working for this. See the result set:

itemno      itemname      Count Sold
1      Pocket knife - Nile      0
3      Compass      1
4      Geo positioning system      2
5      Map measure      0
6      Hat - Polar explorer      1
7      Hat - Polar explorer      0
8      Boots - snake proof      0
9      Boots - snake proof      1
10      Safari chair      1
11      Hammock      1
12      Tent - 8 person      1
13      Tent - 2 person      2
14      Safari cooking kit      1
15      Pith helmet      0
16      Pith helmet      1
17      Map case      0
18      Sextant      0
19      Stetson      1
20      Stetson      0
0
Saurabh Singh TeotiaCommented:
You can use the following query...

select tb1.itemno,tb1.itemname,tb1.[Count Sold]
from 
(SELECT lineitem.itemno, item.itemname,COUNT(lineitem.itemno) AS [Count Sold]
FROM item, lineitem
WHERE item.itemno = lineitem.itemno
GROUP BY lineitem.itemno, item.itemname
Having  COUNT(lineitem.itemno) <3
               
UNION

SELECT item.itemno, item.itemname, 0 AS [Count Sold]
FROM item
WHERE (((item.itemno) Not In (SELECT lineitem.itemno FROm lineitem)))) tb1
order by tb1.[Count Sold]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chonabrahamAuthor Commented:
Great!! So is the first select clause an alias type of table or a view of a table?
0
Saurabh Singh TeotiaCommented:
So the first select is sort of a view of table created dynamically by joining two different queries by union and then since you want to apply sort on the total list..So once it's join then you apply your query for sort so that records for both the queries whey they have merged..then it should do the sorting...
0
chonabrahamAuthor Commented:
Ok, thanks this is extremely helpful!!! I can close it out now.
0
Saurabh Singh TeotiaCommented:
You are welcome... Happy to help ... :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.