Solved

Recursive Query Help

Posted on 2008-10-07
10
192 Views
Last Modified: 2012-05-05
I have this query that gives me the postingFacilityID and total openings.

SELECT DISTINCT j.postingFacilityID, SUM(j.true_job_cnt) AS tot_openings, ff.facilityName
FROM jobInfo j, facilities f
WHERE j.display = 'Y'
            AND j.dateClose >= GetDate()
            AND j.facilityID = f.facilityID
            and f.statusid = 1
            and f.deleted = 0
GROUP BY j.postingFacilityID
ORDER BY SUM(j.true_job_cnt) DESC

What I want is to be able to use the resultant postingFacilityID and get the facility name which would be in the facilities table as "facilityName".
0
Comment
Question by:coldchillin
  • 4
  • 4
  • 2
10 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22661552
You've got a FacilityName in your list of fields ... is it returning the wrong thing?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22661561
Try:


SELECT j.postingFacilityID, f.facilityName, SUM(j.true_job_cnt) AS tot_openings

FROM jobInfo j

inner join facilities f

on j.facilityID = f.facilityID

WHERE j.display = 'Y'

  AND j.dateClose >= GetDate()

  and f.statusid = 1

  and f.deleted = 0

GROUP BY j.postingFacilityID , f.facilityName

ORDER BY SUM(j.true_job_cnt) DESC

Open in new window

0
 
LVL 1

Author Comment

by:coldchillin
ID: 22662314
I tried that as well, but with no luck. The reason why I can't just include it is because I need the DISTINCT item.

The records are grouped by "corporation", where all members belong to the corporate record will be aggregated and displayed under the "corporate" name.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22662495
Can you post a few rows of sample data?  My code would have looked a lot like Brandon's from what I understand ... so I'm missing something.
0
 
LVL 1

Author Comment

by:coldchillin
ID: 22663218
Ok, initial query I posted returns this:
postingFacilityID        tot_openings
11                  559
158                  48
211                   30

The suggested query returns this:
postingFacilityID
11              name1      117
11              name2      102
11              name3      69
11              name4      68
11              name5      50

The problem with the second query is that all those records with the postingFacilityID of 11 should be aggregated. The actual entity with the ID 11 will have the name "Name0"
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22663817
Can you post the definitions of jobInfo and facilities?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22677735
Is there more than one FacilityName for a FacilityID?
0
 
LVL 1

Author Comment

by:coldchillin
ID: 22683164
The facility table has the following relevant fields:
facilityID, corporateID, facilityName

The jobInfo table has the following relevant field:
postingFacilityID

The posted query works fine, but what I then need to do is go back and using the resultant postingFacilityID, match it back to the facilities table to the facilityID, and get the facilityName
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22686556
The posted query works fine, but what I then need to do is go back and using the resultant postingFacilityID, match it back to the facilities table to the facilityID, and get the facilityName
Understood.  It's that matching up that is missing some criterion.


The suggested query returns this:
postingFacilityID
11              name1      117
11              name2      102
11              name3      69
11              name4      68
11              name5      50

The problem with the second query is that all those records with the postingFacilityID of 11 should be aggregated. The actual entity with the ID 11 will have the name "Name0"
Why?  Where does "Name0" come from?

Are you using Brandon's query exactly as he posted it?  Or are you modifying it?  If modifying, please post the modification.  

Perhaps you've simplified the problem for us ... a little too much.
0
 
LVL 1

Author Comment

by:coldchillin
ID: 22689642
Ahhh...I see what happened!

Brandon, the query you posted uses j.facilityid = f.facilityid, but what it needed to use was j.postingFacilityID = f.facilityID
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 71
Not showing page correctly 3 29
Turn on intranet settings 1 36
ASP.net Run 3 sliders vertically as opposed to horizontally 9 18
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

16 Experts available now in Live!

Get 1:1 Help Now