var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: same table uses multiple times in a query
I have a UserService table with the following data
userID, Service, Access
111, Email, Y
111, Wireless, N
I want to return the Access value of the two services that userID(111) have.
I tried the following query
It appears to work. However, I was wondering if there is any better way to do this? I think my query is not very efficient.
select a.Access, b.Access
from UserService a, UserService b
min(case when Service='Email' then Access end) Email
,min(case when Service='Wireless' then Access end) Wireless
from -- YourTable
111, 'Email' , 'Y' union all select
111, 'Wireless', 'N' )t(userID, Service, Access)
The cheapest would be selecting the two rows with only one access to the table. Note that this will return two result rows.
select Service, Access
and Service in ('Email', 'Wireless')