tricky SQL - bitwise operators, self-joins 2

[gusset]

this is slightly above my ability. i guess there must be an elegant solution to this but i don't know what it might be and i suspect that even if i did, i wouldn't know how to implement it, so any help very gratefully received.

i have, in my table, an integer field which stores a number corresponding to which category (or categories) a record falls into. let's say the categories are "A", "B", "C" or "none".

0 - none
1 - A
2 - B
4 - C

so, e.g. if a document falls into categories A and C, the int field stores 5, the sum of 1 and 4.

then, to find out which categories a record falls into, i select on a bitwise result.

e.g.
select
record_id,
case (category & 1) when 0 then '' else 1 end as A,
case (category & 2) when 0 then '' else 1 end as B,
case (category & 1) when 0 then '' else 1 end as C
where category > 0
order by record_id

...which gives me a recordset with as many records as are categorised, with a boolean value for each category

(when i was doing this, i thought it was very elegant and clever. i guess this is nothing new to lots of you, though!)

now, i need to list all the records, grouping them by category. if a document has more than one category selected, it should appear under each category.

i guess i need a self-join and a subquery for this, but as i say, i can't quite work out the concept.

here is an example of the recordset produced by the above code and then what i want to achieve.
what i have...
[tt]

fun hard sweaty
golf 1 1
pool 1
soccer 1 1
runnng 1
[/tt]

what i want...
[tt]
golf fun
pool fun
soccer fun
golf hard
soccer sweaty
running sweaty
[/tt]

many thanks for any ideas!

 

[royjimenez]

i haven't done something seem in SQL, but maybe u can do this: ( pseudocode )

for i = 1 to 8 // i will test 8 bits
select *
from mytable
where ( category & i ) = i
next

BUT i think that u should make other table to keep the categories.

Regards.

 

[JamesLean]

This is NOT a good way to store this information. If you can, I would change you tables to these:

Document
DocumentID : PK
...

Category
CategoryID : PK
CatName
...

DocCategory
DocumentID : FK to Document table
CategoryID : FK to Category table

So you store your documents in one table, your categories in another and then the links between them in the third table. This is the standard method of storing many-to-many relationships in a normalised database and will make querying the data much easier.

--James

 

[maswien]

table1:

game category
---------- -----------
golf 3
pool 1
soccer 5
runnng 4


table2:

comment category
-------------------- -----------
fun 1
hard 2
sweaty 4


SQL:

select game, comment from table1, table2
where table1.category & table2.category > 0
order by comment, game

results:

game comment
---------- --------------------
golf fun
pool fun
soccer fun
golf hard
runnng sweaty
soccer sweaty

(6 row(s) affected)

 

[maswien]

Actually, table2 is not needed:


select game, comment from table1 t1,
(select 'fun' as comment, 1 as category
union
select 'hard' as comment, 2 as category
union
select 'sweaty' as comment, 4 as category) as t2
where t1.category & t2.category > 0
order by comment, game

 

[gusset]

thanks for all your comments. jameslean, i have noted the point for the future.

gusset