• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Selecting the table twice

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have got a query which looks for
a) a superset of all data in the database if the role is supervisor
b) a subset of data if the role is worker

So I query it with the confinement of the roles by a bitmask

say
super = 4
worker = 2

So selecting 4 will select the super
and 2 will select the worker

However, when I was joining the table with itself, hence the viewer can be one of the workers listed in the
final result hence

Viewer Amy : Super

Job
1 Amy abc
2 Tom cdf

Employee
1 Amy
2 Tom


I have to query the table twice because if I do something like



I will get the same person with all the jobs.
So I need to do



Not exactly runnable, but you get the idea, will the performance of such query suffer whenever the
situation is, I can't think of one, but can you? or are there any potential problems in such a query?
Any help would be greatly appreciated
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depending on your database you will quite possibly not be able to add an index that will match using a bitmask, so performance will suffer in that respect.

Not sure why you think a bitmask makes sense over a table of roles and a simple join.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic