Solved

Bringing back rows - help with this sql

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2 comma seperated list - SQL Server 12 40
SQL Server 2012 express 24 37
How to order Highest and lowest value rows alternatively in SQL Server ? 4 52
Help Required 2 32
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 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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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