Solved

Bringing back rows - help with this sql

Posted on 2011-09-17
5
370 Views
Last Modified: 2012-05-12
In this example, i need to bring back all 3 rows in SignupHCProvider table. This is because in OfficeUserName ...businssNameId = MainOfficeId = 3

I got it working like this but only brings back the 2 first rows (the rows businessNameId=3) but i need the one with row 39 as well

---SignupHCProvider
BusinessNameId   FirstName   LastName
  3                bob         jones
  3                aa          bbb
  39               xx          ss

---OfficeUserName
   username       BusinessNameId   MainOfficeId  Active
    admin           3                1            1
    vvv             39               3            1
select hc.firstname + ' ' + lastname,
        oun1.mainofficeId , hc.businessNameId

 from dbo.SignupHCProvider hc
   inner join dbo.OfficeUserName oun1 on oun1.BusinessNameId = hc.BusinessNameID
        inner join dbo.OfficeUserName un2
                   on oun1.businessnameid = un2.mainofficeid
  
                where oun1.UserName = 'admin'
                     --and  oun1.mainofficeId = hc.businessNameId
                    and hc.Active = 1

Open in new window

0
Comment
Question by:Camillia
  • 3
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36555413
It sounds like one of the joins needs to be an OUTER JOIN. Which table does not have a row for businessNameId = 39? Whichever table does not needs to be on the right of a LEFT OUTER JOIN.
0
 
LVL 7

Author Comment

by:Camillia
ID: 36556267
let me try
0
 
LVL 7

Author Comment

by:Camillia
ID: 36556399
No, I dont know. I can break up into temp tables but there has to be a way to do this. I created the data for you if you can try it;

Create table #SignupHCProvider
 (
   BusinessNameId  int,
    firstname varchar(10),
    lastname varchar(10)

 )

create table #OfficeUserName
(
   username varchar(10),
   BusinessNameId  int,
   MainOfficeId    int,
   Active bit
)

insert into #SignupHCProvider
  select 3, 'bob','jones'

insert into #SignupHCProvider
  select 3, 'aa','bbb'

insert into #SignupHCProvider
  select 39, 'xx','ss'


insert into #OfficeUserName
  select 'admin', 3, 1, 1

insert into #OfficeUserName
  select 'vvv', 39, 3, 1
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36556563
Okay, I see. The row with 39 is being filtered by the oun1.UserName = 'admin' so is not showing as a row. Because you have JOIN'd on mainofficeid, you are getting the 'vvv' username on the same line as the other two.

select hc.firstname + ' ' + lastname,
        oun1.mainofficeId , hc.businessNameId, un2.BusinessNameId
 from #SignupHCProvider hc
   inner join #OfficeUserName oun1 on oun1.BusinessNameId = hc.BusinessNameID
        inner join #OfficeUserName un2
                   on oun1.businessnameid = un2.mainofficeid
                where oun1.UserName = 'admin'

If what you need instead is to grab the top-level rows and then add in all its brand offices, then you will need a recursive CTE or a something similar.

;with offices(HCProvider, MainOfficeID, BusinessNameID) as (
   /* anchor or base query to get 'admin' offices */
   select hc.firstname + ' ' + lastname, oun1.mainofficeId , hc.businessNameId
   from #SignupHCProvider hc
   inner join #OfficeUserName oun1 on oun1.BusinessNameId = hc.BusinessNameID
   where oun1.UserName = 'admin'

   union all /* initiates recursion */

   /* query to get branch offices */
   select hc.firstname + ' ' + lastname, oun1.mainofficeId , hc.businessNameId
   from #SignupHCProvider hc
   inner join #OfficeUserName oun1 on oun1.BusinessNameId = hc.BusinessNameID
   inner join offices o on o.businessnameid = oun1.mainofficeid
)
select distinct HCProvider, MainOfficeID, BusinessNameID
from offices
;

Open in new window


Hope that helps!
0
 
LVL 7

Author Comment

by:Camillia
ID: 36556768
yes, that worked, thanks.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now