join question

[FALCONSEYE]

I have the following:

select * 
from  categories c, forums f, topics t
where c.category_id = f.category_id (+)
and t.forum_id (+) = f.forum_id
and (  c.is_removed = 0 and  f.is_removed = 0 and t.is_removed = 0 )

the mgmt decided not to delete the records and just include a flag. if it's deleted is_removed becomes 1.
now the problem is the join gets screwed up when i include the is_removed condition with 0. i have 5 categories with 14 forums and only 2 topics in one forum. all of the is_removed fields are 0.

the query returns 14 results without the is_removed condition and only 1 with is_Removed condition.
what's the correct way of writing this query?

http://www.fuzzysiberians.com

 

[FALCONSEYE]

nevermind. i have to write this in a different way. if i do :

and (  c.is_removed = 0 and nvl(f.is_removed, 0) = 0 and nvl(t.is_removed, 0) = 0 )

then i get all 14 records.

http://www.fuzzysiberians.com

 

[Dagon]

You could also use inline views or proper views to get round the problem. Companies that I've seen that have used this technique have normally had a view which incorporates the deleted flag and you're supposed to use that rather than the original table.

Personally, I think the idea of having a deleted flag in a table is a recipe for disaster. You can guarantee that not everyone will know about the flag and that someone will forget to incorporate it. What will happen is that a record will be deleted then added back with a different surrogate key. You then get queries returning duplicate rows because someone has forgotten to include the flag. Also, do you really want a whole load a useless deleted rows clogging up your data and making queries take longer than they need to?

Tell your management it's a stupid idea and that if they want to keep the data, they should do it properly using archive or audit tables.



For Oracle-related work, contact me through Linked-In.

 

[FALCONSEYE]

love it:

Tell your management it's a stupid idea and that if they want to keep the data, they should do it properly using archive or audit tables.

http://www.fuzzysiberians.com