Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    32

    Unanswered: same table uses multiple times in a query

    Hi
    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

    Code:
    select a.Access, b.Access
    from UserService a, UserService b
    where a.userID='111'
    and b.userID='111'
    and a.Service='Email'
    and b.Service='Wireless'
    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.

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select 
     min(case when Service='Email' then Access end) Email 
    ,min(case when Service='Wireless' then Access end) Wireless 
    from -- YourTable
                          (select 
    111, 'Email'   , 'Y'   union all select
    111, 'Wireless', 'N'  )t(userID, Service, Access)
    
    where userID=111

  3. #3
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    The cheapest would be selecting the two rows with only one access to the table. Note that this will return two result rows.

    Code:
    select Service, Access
    from UserService
    where userID='111'
    and Service in ('Email', 'Wireless')
    Regards,
    Mariano Corral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •