Sorting by category

[itsjames]

Hello,

I have a database where items can have more than one categroy, similar to this:

itemcat1cat2cat3

1ABC

2BAC

3BA

and so on

 

If I were to query my database to show all items in (for example) category B, what query would I need to use so that items with cat1 set to B are displayed first? For example, if we were to use this query on the above table, items 2 and 3 would display first, and item 1 last.

 

Apologies if this is hard to understand, it took me long enough to think of how to word it!

[kickstart]

Hi

 

Crude way to do it

 

SELECT 'Cat1' as Cat, Item FROM Table WHERE Cat1 = 'B'
UNION
SELECT 'Cat2' as Cat, Item FROM Table WHERE Cat2 = 'B'
UNION
SELECT 'Cat3' as Cat, Item FROM Table WHERE Cat3 = 'B'
ORDER BY Cat[code]

Probably a better way to do it:-

[code]
SELECT Item,
(CASE 
WHEN Cat1  = 'B' THEN 1
WHEN Cat2  = 'B' THEN 2
WHEN Cat3  = 'B' THEN 3
ELSE 0
END ) AS Cat
FROM loanee_table
WHERE Cat1 = 'B' OR Cat2 = 'B' OR Cat3 = 'B'
ORDER BY Cat

 

Untested variation on above:-

 

SELECT Item,
(CASE 
WHEN Cat1  = 'B' THEN 1
WHEN Cat2  = 'B' THEN 2
WHEN Cat3  = 'B' THEN 3
ELSE 0
END ) AS Cat
FROM loanee_table
WHERE 'B' IN (Cat1,Cat2,Cat3)
ORDER BY Cat

 

However I would be inclined to have a very different solution. Have a table of items and then a table listing the Item, category and importance. Something like:-

 

Item Table

Item Desc

1 Saw

2 Drill

3 Hammer

 

ItemCategory Table

Item Category Importance

1 A Cat1

1 B Cat2

1 C Cat3

2 B Cat1

2 A Cat2

2 C Cat3

3 B Cat1

3 A Cat2

 

Then do something like (not tested):-

 

SELECT a.Item, b.Importance
FROM Item a
INNER JOIN ItemCategory b 
ON a.Item = b.Item
WHERE b.Category = 'B'
Order By b.Importance

 

This would mean you could add as many categories as you want in future without having to change the table layout.

 

All the best

 

Keith