?
Solved

Bringing back rows - help with this sql

Posted on 2011-09-17
5
Medium Priority
?
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

765 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