Solved

Bringing back rows - help with this sql

Posted on 2011-09-17
5
376 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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