Solved

Bringing back rows - help with this sql

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Divide by zero error encountered. 2 45
Query group by data in SQL Server - cursor? 3 64
Need to find substring in SQL 5 41
Can I skip a node in XML? 9 34
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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.

749 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